QTP10.com is a new and completely free website that offers help in all the versions of HP QTP (including latest version 10). More importantly, it offers free code for users of HP QuickTest Professioal. This site helps you in learning the basics of QTP and descriptive/advanced programming in (QTP) on various environments like - .Net, Infragistics, Web, Windows, etc.

Note -
This blog is NOT affiliated with HP / Hewlett-Packard in any way. The data/questions come from various sources and we have our own testing questions. I am just another Software Tester like you.

QTP Code - Execute Stored Procedures

Share This Post -

Sponsored Links:

 

 

Recently one reader ask me this question. So here are some codes that should help you execute a stored procedure.

As per wiki -

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, or SP) are actually stored in the database data dictionary.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.

 

 

Code example 1:

 

' set the parameters of your database here
strDatabaseName = ""
strUser = ""
strPassword = ""
strStoredProcedureName = ""

' create the database object
Set objDB = CreateObject("ADODB.Command")

 

' set the connection string
objDB.ActiveConnection =
"DRIVER={Microsoft ODBC for Oracle}; " & _
                         "SERVER=" & strDatabaseName & _
                         ";User ID=" & strUser & ";Password=" & strPassword & " ;"

' set the command type to Stored Procedures
objDB.CommandType = 4
objDB.CommandText = strStoredProcedureName

' define Parameters for the stored procedure
objDB.Parameters.Refresh

 

' set parameters for stored procedure (i.e. two parameters here)
objDB.Parameters(0).Value = "Param1"
objDB.Parameters(1).Value = "Param2"

' execute the stored procedure
objDB.Execute()

 

' destroy the object
Set objDB = Nothing

 

How to Call SQL stored procedure from QTP?


Function RunStoredProcedure(StoredProcedureName)
sDatabaseName=”ABC”
sUID=”xyz”
sPWD=”ABC_xyz” ‘ Create the database object
Set cm = CreateObject(”ADODB.Command”)
‘ Activate the connection.
cm.ActiveConnection = “DRIVER={Microsoft ODBC for Oracle}; ” &_
“SERVER=” & sDatabaseName & “;User ID=” & sUID & “;Password=” & sPWD & ” ;”
‘ Set the command type to Stored Procedures
cm.CommandType = 4
‘ Stored Procedures
cm.CommandText = StoredProcedureName
‘ Define Parameters for the stored procedure
cm.Parameters.Refresh
‘ Pass input value. Assuming Stored Procedure requires 2 parameters
cm.Parameters(0).Value = “Kuldeep”
cm.Parameters(1).Value = “Kumar”
‘ Execute the stored procedure
cm.Execute()
Set cm = Nothing
End Function