cancel
Showing results for 
Search instead for 
Did you mean: 

How to Execute SQL Stored Procedure with Parameters on TestComplete

SOLVED
Highlighted
Occasional Contributor

How to Execute SQL Stored Procedure with Parameters on TestComplete

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: How to Execute SQL Stored Procedure with Parameters on TestComplete

Hi @Yan01,

 

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

 

 

---------
Tanya Gorbunova
SmartBear Community Manager

 Join SmartBear Connect next April in Boston to meet with software development, API and testing experts from around the world! Get your ticket today!

View solution in original post

3 REPLIES 3
Community Manager

Re: How to Execute SQL Stored Procedure with Parameters on TestComplete

Hi @Yan01,

 

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

 

 

---------
Tanya Gorbunova
SmartBear Community Manager

 Join SmartBear Connect next April in Boston to meet with software development, API and testing experts from around the world! Get your ticket today!

View solution in original post

Occasional Contributor

Re: How to Execute SQL Stored Procedure with Parameters on TestComplete

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? 

Community Manager

Re: How to Execute SQL Stored Procedure with Parameters on TestComplete

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.

---------
Tanya Gorbunova
SmartBear Community Manager

 Join SmartBear Connect next April in Boston to meet with software development, API and testing experts from around the world! Get your ticket today!