Forum Discussion

culisse's avatar
culisse
Contributor
13 years ago

Retrieve recordset after executing a Stored Procedure?

Hello,

How do I get the returned recordset data into a table variable after executing a Stored Procedure??  The only documentation on TestComplete for executing a Stored Procedure is below and talks about the Input parameters but nothing on how to retrieve the results.  I've tried all sorts of ways to set my table variable equal to the execution of the Stored Proc but it seems like the results are always empty.  Thanks for your help.

Chris


Sub CreatingStoredProcedure

  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

  'Call 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)

  ' Call Log.Message("Result: " &_

  ' SProc.Parameters.ParamByName("RETURN_VALUE").Value)

End Sub




  • culisse's avatar
    culisse
    Contributor
    13 years ago
    Okay - our development team got me on the right track and I can now obtain Stored Procedure results.  SmartBear only gave the example with "CreateADOStoredProc" which will only get you a return value.  In order to obtain the results of the StoredProcedure, use "CreateADODataset".  Below is an example...



    Sub CallStoredProc()

            ' Create the new IAQAADODataset object

            Set DSet = ADO.CreateADODataset

            ' Specify the connection string

            DSet.ConnectionString = "..."  'Insert your connection string here

            ' Specify the command type and text

            DSet.CommandType = cmdStoredProc

            DSet.CommandText = "..."     'Insert Stored Proc name here

                ' Adding an input parameter if required

            Call DSet.Parameters.AddParameter()

            DSet.Parameters.Items(0).name = "..."

            DSet.Parameters.Items(0).DataType = "..."

            DSet.Parameters.Items(0).Direction = adParamInput

            DSet.Parameters.Items(0).Value = "..."

            ' Open the dataset

            DSet.Open()

            DSet.First()     'Just sample here of a value from the first record of the result set of the Stored Proc

            varCount = DSet.FieldByName("VisitCount").Value

            Call Log.Message("Count: " & varCount)

            DSet.Close()

    End Sub

  • sagar_pavitwar's avatar
    sagar_pavitwar
    11 years ago
    I am able to get RecordSet object . But it has only 256 row but  the SP which I am calling returns lacs of rows. Is there any way to get all the  rows into recordset object.