[Collapse]TestComplete How To
 [Expand]Microsoft Office
 [Expand]Miscellaneous
 [Expand]Network
 [Expand]OpenOffice
 [Collapse]SQL
   Add records to an SQL table/viewarticle/9013/
   Call an SQL Stored Procedure/viewarticle/9014/
   Create an SQL Stored Procedure/viewarticle/9025/
   Create and drop SQL databases/viewarticle/8968/
   Create SQL tables/viewarticle/9007/
   Drop an SQL Stored Procedure/viewarticle/9026/
   Drop SQL tables/viewarticle/9008/
   Files in SQL tables (read and write)/viewarticle/8969/
   Get all databases' names /viewarticle/17636/
   Update records in SQL tables/viewarticle/18441/
 [Expand]TestComplete Utility Scripts
 [Expand]Third-party Controls
 [Expand]Web Utility Scripts
 [Expand]Windows Utility Scripts
Updated: 4/24/2012 Applies To: TestComplete Rating: GoodGoodGoodGoodGood 12 vote(s) Click to rate: PoorNot badAverageGoodExcellent
Call an SQL Stored Procedure

The script below demonstrates how to call an SQL stored procedure which has two input parameters and how to get a result parameter. Besides that, if you use output parameters in stored procedures, you can also get their values from script.

VBScriptCopy Code

Sub Test
  Dim SProc
  
  Set 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
  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
  
  ' Adding an in parameter
  Call 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
  Call 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 = adSmallInt
  'SProc.Parameters.Items(2).Direction = adParamOutput  
  'SProc.Parameters.Items(2).Value = null
   
  ' Running the procedure
  Call SProc.ExecProc()
  
  ' Obtaining the return value 
  Call Log.Message("Result: " & SProc.Parameters.Items(0).Value)
  ' Log.Message("Result: " &_
  '            SProc.Parameters.ParamByName("RETURN_VALUE").Value)
End Sub

JScriptCopy Code

function Test()
{
  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 = adInteger;
  SProc.Parameters.Items(0).Direction = adParamReturnValue;  
  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 = adSmallInt;
  SProc.Parameters.Items(2).Direction = adParamOutput;  
  SProc.Parameters.Items(2).Value = null;  */
  
  // Running the procedure
  SProc.ExecProc();
 
  // Obtaining the return value
  Log.Message("Result: " + SProc.Parameters.Items(0).Value);
  // Log.Message("Result: " + 
  //    SProc.Parameters.ParamByName("RETURN_VALUE").Value);
}


© 2019 SmartBear Software. All rights reserved.
Email Send feedback on this document