Quick test professional

QTP Tips QTP codes QTP Faqs and more

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

Advertisements

February 14, 2008 - Posted by | Running stored procedures from QTP | , , , , , , , ,

1 Comment »

  1. we want to get the connection string from Environment or datatable, but it has db password and has to be secure. I don’t see a way to encrypt the string before storing it on datatable etc, and then have it encrypted after we get the connect string from there.
    Thanks for the great work!

    Comment by label klahr | September 4, 2009 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: