Ask a Question

How to Execute SQL Stored Procedure with Parameters on TestComplete

SOLVED
Yan01
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
TanyaYatskovska
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 Yatskovskaya
SmartBear Community and Education Manager

Join Wintertainment 2021 to share your stories, have fun, earn community badges, and more!

View solution in original post

3 REPLIES 3
TanyaYatskovska
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 Yatskovskaya
SmartBear Community and Education Manager

Join Wintertainment 2021 to share your stories, have fun, earn community badges, and more!

View solution in original post

Yan01
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? 

TanyaYatskovska
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 Yatskovskaya
SmartBear Community and Education Manager

Join Wintertainment 2021 to share your stories, have fun, earn community badges, and more!
cancel
Showing results for 
Search instead for 
Did you mean: