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
