Running stored procedures from QTP

Running stored procedures from QTP 

Public Function BIP_sqlRunStoredProcedure (sSProcName, sParameter1, sParameter2, sParameter3)

‘ Create the database object
Set oADO_CMD = CreateObject(“ADODB.Command”)

‘Get connection string
sConnectionStr = Environment(“SQL_ConnectionStr”)
‘ Activate the connection
oADO_CMD.ActiveConnection = sConnectionStr

‘ Set the command type to Stored Procedures
oADO_CMD.CommandType = 4
oADO_CMD.CommandText = sSProcName

‘ Define Parameters for the stored procedure
oADO_CMD.Parameters.Refresh
‘ The order of input output values is the same order as defined in the stored procedure

‘Based on the qty of parameters (if any) for this sproc …
‘This maps to the [optional] aspect of the function – not all sprocs have parameters.
‘Note – in the ADO object model / Parameters collection (0) is reserved for the return value; the first parameter therefore is (1).
If “” <> sParameter1 Then
‘ Pass FIRST input value [optional]
oADO_CMD.Parameters(1).Value = sParameter1
‘msgbox oADOConnection.Parameters(1).Name +vbcr+ oADOConnection.Parameters(1).Value ‘DEBUG

If “” <> sParameter2 Then
‘ Pass SECOND input value [optional]
oADO_CMD.Parameters(2).Value = sParameter2
‘msgbox oADOConnection.Parameters(2).Name +vbcr+ oADOConnection.Parameters(2).Value ‘DEBUG

If “” <> sParameter3 Then
‘ Pass THIRD input value [optional]
oADO_CMD.Parameters(3).Value = sParameter3
‘msgbox oADOConnection.Parameters(3).Name +vbcr+ oADOConnection.Parameters(3).Value ‘DEBUG
End If
End If
End If

‘ Execute the stored procedure
oADO_CMD.Execute()

‘Clean up objects
Set oADO_CMD = Nothing

End Function

  Source: Mercury Forum’s KB articles

Running stored procedures from QTP

Running stored procedures from QTP 

Public Function BIP_sqlRunStoredProcedure (sSProcName, sParameter1, sParameter2, sParameter3)

‘ Create the database object
Set oADO_CMD = CreateObject(“ADODB.Command”)

‘Get connection string
sConnectionStr = Environment(“SQL_ConnectionStr”)
‘ Activate the connection
oADO_CMD.ActiveConnection = sConnectionStr

‘ Set the command type to Stored Procedures
oADO_CMD.CommandType = 4
oADO_CMD.CommandText = sSProcName

‘ Define Parameters for the stored procedure
oADO_CMD.Parameters.Refresh
‘ The order of input output values is the same order as defined in the stored procedure

‘Based on the qty of parameters (if any) for this sproc …
‘This maps to the [optional] aspect of the function – not all sprocs have parameters.
‘Note – in the ADO object model / Parameters collection (0) is reserved for the return value; the first parameter therefore is (1).
If “” <> sParameter1 Then
‘ Pass FIRST input value [optional]
oADO_CMD.Parameters(1).Value = sParameter1
‘msgbox oADOConnection.Parameters(1).Name +vbcr+ oADOConnection.Parameters(1).Value ‘DEBUG

If “” <> sParameter2 Then
‘ Pass SECOND input value [optional]
oADO_CMD.Parameters(2).Value = sParameter2
‘msgbox oADOConnection.Parameters(2).Name +vbcr+ oADOConnection.Parameters(2).Value ‘DEBUG

If “” <> sParameter3 Then
‘ Pass THIRD input value [optional]
oADO_CMD.Parameters(3).Value = sParameter3
‘msgbox oADOConnection.Parameters(3).Name +vbcr+ oADOConnection.Parameters(3).Value ‘DEBUG
End If
End If
End If

‘ Execute the stored procedure
oADO_CMD.Execute()

‘Clean up objects
Set oADO_CMD = Nothing

End Function