ExecSp Method

• The ExecSp() method call will fail if the stored procedure is not a valid MES database stored procedure. So the call will fail if the stored procedure being called is a SQL System stored procedure or a stored procedure that is not in the MES database.
• The method will search the supplied parameter list looking for a session_id parameter. If a session_id parameter is found, the value will be passed to the XMLReadBuilder or XMLExecBuilder constructor as the sessionID value. For the call to succeed, the session_id parameter value must be an integer and must be in the Sessn table.
• When calling a custom stored procedure and a parameter is not included in this method call, the middleware will call the stored procedure with a null value for any missing parameter. The method does not automatically replace the null with the default value defined in the stored procedure. When using parameters for this method, all parameters must be supplied. Any missing parameters will be set to null by the MES middleware and will not use any defaults defined in the stored procedure. The stored procedure can be programmed to detect a null and then set a default value as is done in many of the MES stored procedures.
• For information about datetime and decimal separator requirements when passed in strings, see the overview topic Datetime and Decimal Format Requirements.

A stored procedure that takes no parameters could be called as follows:

• DirectAccess.ExecSp("MySp");

A stored procedure that takes one parameter could be called as follows:

• DirectAccess.ExecSp("MySp", new SpParameter("param_name", param_value));

A stored procedure that takes two parameters could be called as follows:

• DirectAccess.ExecSp("MySp", new SpParameter("param_name1", param_value1), new SpParameter("param_name2", param_value2));

The following example code is provided below:

• SQL code to create a custom stored procedure named "TestExecSp".
• An example of an ExecSp() method calling the custom stored procedure TextExecSp. The custom stored procedure requires one parameter.
• A System Platform scripting example of an ExecSp() method calling a stored procedure named "TestSP" that has one parameter named Param1 and an output parameter named Count1.
• A System Platform scripting example of an ExecSp() method calling a stored procedure named "sp_I_Test" with no parameters.

SQL Code for Custom Stored Procedure for ExecSp() Example (SQL Code)

CREATE PROCEDURE [dbo].[TestExecSp]

(@return_null BIT

,@bool_val BIT OUTPUT

,@datetime_val DATETIME OUTPUT

,@decimal_val FLOAT OUTPUT

,@double_val FLOAT OUTPUT

,@int_val INT OUTPUT

,@string_val VARCHAR(11) OUTPUT

)AS

BEGIN

IF (@return_null = 0)

SELECT @bool_val = 1,

@datetime_val = Convert(DATETIME, Convert(VARCHAR, GetDate(), 111) + ' 09:00:00', 120),

@decimal_val = 99999999999.999,

@double_val = 5.35,

@int_val = 92,

@string_val = 'Test String'

ELSE

SELECT @bool_val = NULL,

@datetime_val = NULL,

@decimal_val = NULL,

@double_val = NULL,

@int_val = NULL,

@string_val = NULL

END

Visual Studio Example of ExecSp() Call Using the Sample Custom Stored Procedure

bool boolVal;

DateTime dateTimeVal;

double doubleVal;

int intVal;

string stringVal;

SpReturnValues returnValues = FactMES.API.Core.DirectAccess.ExecSp("TestExecSp", new SpParameter("return_null", false));

boolVal = returnValues.GetReturnValueAsBoolean("bool_val");

dateTimeVal = returnValues.GetReturnValueAsDateTime("datetime_val");

doubleVal = returnValues.GetReturnValueAsDouble("double_val");

intVal = returnValues.GetReturnValueAsInt("int_val");

stringVal = returnValues.GetReturnValueAsString("string_val");

System Platform Script Example ExecSp() Calling the Custom Stored Procedure TestSp with Param1 and Count1 Parameters

dim ReturnValues as aaMES.Core.aaSpReturnValues;

dim Param1 as aaMES.Core.aaSpParameter;

dim parameters[2] as aaMES.Core.aaSpParameter;

dim Result as aaMES.Result;

dim output as System.Int32;

Param1 = new aaMES.Core.aaSpParameter("Param1", 5);

parameters[1] = Param1;

{ aaMES.Core.aaDirectAccess.ExecSp( spName, parameters );}

Result = aaMES.Core.aaDirectAccess.ExecSp( "TestSP", parameters );

if Result.Success then

ReturnValues = Result.aaSpReturnValues_Value;

Result = ReturnValues.GetReturnValueAsInt("count1"); {return parameters are in lower case}

me.Count = Result.Int32_Value;

Result = ReturnValues.IsReturnValueNull("count1");

me.IsNullReturn = Result.Boolean_Value;

Me.LogMessage = "Success, count = " + Me.Count;

else

Me.LogMessage = "Error: " + Result.Exception.Message;

endif;

System Platform Script Example ExecSp() Calling the Custom Stored Procedure sp_I_Test with No Parameters (Note that System Platform scripting requires the passing of the second (SPParameter) parameter to the method. Any value can be passed as it is ignored.)

dim result as aaMES.Result;

dim SPParam[1] as aaMES.Core.aaSpParameter;

SPParam[1] = new aaMES.Core.aaSpParameter("test","test");

result = aaMES.Core.aaDirectAccess.ExecSp( "sp_I_Test", SPParam );

Datetime and Decimal Format RequirementsParam1Count1

Datetime and Decimal Format RequirementsParam1Count1

Reference

DirectAccess Class
DirectAccess Members