How to Execute SQL Stored Procedure with Parameters on TestComplete

[Yan01]

Currently we need to run TestComplete to execute a stored procedure with parameters, when I run it in SQL MS, it looks like this:

EXEC ar_sp_VarGroupUpdateXML @i_UserCode='mgb',

@i_TerminalCode='',

@i_TranCode='stdpr118mf',

@i_XmlDoc=N'<VariantData>
<VarGroups>
<VarGroup typeCode="C" code="BYW" description="BYW" colorRGB="" numberOfVariants="1" numberOfDimensions="1" dimension1TypeValue="1" isUpdateRequired="true"/>
</VarGroups>
<VarCodes>
<VarCode code="YELLOW3" description="Yellow 3" groupCode="BYW" groupDescription="" code1="" code2="" nrfCode="" colorRGB="" typeCode="C" isUpdateRequired="true" />
<VarCode code="YELLOW4" description="Yellow 4" groupCode="BYW" groupDescription="" code1="" code2="" nrfCode="" colorRGB="" typeCode="C" isUpdateRequired="true" />
</VarCodes>
</VariantData>',

@i_AuditMessage1=N'1D group [<GROUP_CODE_OLD>] has been created.',

@i_AuditMessage2=N'1D group [<GROUP_CODE_OLD>] has been updated.',

@i_AuditMessage3=N'Colour [<VAR_CODE>] in group [<GROUP_CODE_OLD>] has been created.',

@i_AuditMessage4=N'Colour [<VAR_CODE>] in group [<GROUP_CODE_OLD>] has been updated.',

@i_AuditMessage5=N'Colour [<VAR_CODE>] in group [<GROUP_CODE_OLD>] has been deleted.',

@i_AuditMessage6=N'Colour [<VAR_CODE>] in group [<GROUP_CODE_OLD>] was moved to group [<GROUP_CODE_NEW>].'

I read some documentations on web, the format looks like this:

function CreatingStoredProcedure()

{
var SProc;
  SProc = ADO.CreateADOStoredProc();
  SProc.ConnectionString = "Provider=SQLNCLI;Server=SERVER_NAME;" + 
                 "Database=DATABASE_NAME;Uid=USER_NAME; Pwd=PASSWORD;";
  SProc.ProcedureName = "dbo.SelectFromTable";
    // Adding a return parameter
  SProc.Parameters.AddParameter(); 
  SProc.Parameters.Items(0).name = "RETURN_VALUE";
  SProc.Parameters.Items(0).DataType = ftInteger; 
  SProc.Parameters.Items(0).Direction = pdReturnValue; 
  SProc.Parameters.Items(0).Value = null; 
    // Adding an in parameter:
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(1).name = "TableName"; 
  SProc.Parameters.Items(1).DataType = ftFixedChar;
  SProc.Parameters.Items(1).Size = 32; 
  SProc.Parameters.Items(1).Value = "chicken"; 
    // Adding an in parameter:
  SProc.Parameters.AddParameter(); 
  SProc.Parameters.Items(2).name = "Fields";
  SProc.Parameters.Items(2).DataType = ftFixedChar; 
  SProc.Parameters.Items(2).Size = 32;
  SProc.Parameters.Items(2).Value = "Caption"; 
    // Adding an out parameter 
  /*
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(2).name = "OutParamName"; 
  SProc.Parameters.Items(2).DataType = ftSmallint;
  SProc.Parameters.Items(2).Direction = pdOutput; 
  SProc.Parameters.Items(2).Value = null; 
  */
    // Running the procedure
  SProc.ExecProc(); 

I tried to use this format to execute SP, but it doesn't work, how should I execute SP with multiple parameters inside?

[TanyaYatskovska · Accepted solution]

Hi @Yan01,

Please refer to the Call and SQL Stored Procedure article - it contains a ready-to-use script.

[Yan01]

Hi:

 I read through the article, but not sure this part: Call SProc.Parameters.AddParameter() SProc.Parameters.Items(0).name = "RETURN_VALUE" SProc.Parameters.Items(0).DataType = adInteger SProc.Parameters.Items(0).Direction = adParamReturnValue SProc.Parameters.Items(0).Value = Null

For example: if I have a stored procedure A that has multiple parameter B, C, and D, then it has multiple lines of AddParameter function from 0 - 2? Another question, in the example script, there are multiple variables: DataType, Size, Direction, Value, what is the meaning of each one? If one of the paramater has value of XML format, how is it working? 

[TanyaYatskovska]

Hi @Yan01,

A number of the AddParameter methods should be the same as a number of parameters you want to declare. Plus, make sure that you declared that out parameter (if a stored procedure returns something).

As for other variables, you can find a detailed description in the Working with Stored Procedure Parameters article of the VCL documentation.