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

Advertisements

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

8 Comments »

  1. I run my SP same like in the example
    when I try to read from the record-set I get error that the object close , any Idea?

    Comment by avishai | March 25, 2009 | Reply

  2. Hi Mohan,

    This is excellent, But my stored procedure is inside the package. When am trying to run am getting error: “[Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation”, at first time so am running manually.next time when am running with script it’s running fine.Can u please provide me the solution if u have an idea.

    Thanks in advance,

    Regards,
    Sharath.

    Comment by Sharath | April 28, 2009 | Reply

  3. hi,

    This talks only about executing the stored proc which has no return or out params in it.

    How to handle the result sets when the procedure has “OUT” params in it?

    Thanks,
    g

    Comment by Geetha | July 22, 2009 | Reply

  4. Buddy when i run the obove code i get stuck at
    >>oADO_CMD.Parameters(1).Value = sParameter1
    says -item cannot be found in the collection corresponding to the requested name or ordinal

    My stored procedure takes 2 input parameters.

    Comment by biz | October 7, 2009 | Reply

  5. Hi,
    Thx for the Code snippet.
    But it would be really appreciated if you could tell why the error which shows that the record set object is closed comes up,even when it is open?
    I am trying to save the values returned by the record set in an excel sheet.

    Comment by Bharath | December 1, 2009 | Reply

  6. Mohan,

    I am trying to execute Store Proc from QTP as above u have specified, Script runs but Store Proc is NOT updating the DB can u just guide me
    Store Proc:
    ————
    exec dbo.tp_pmt_inbound_process_ack
    @arg_ackmsgid=’89909′,
    @arg_ackmsgtime=’2009-12-10T05:02:09′,
    @arg_ackmsginitiator=’uhushf’,

    QTP Script:
    ————
    Set dbConnection = CreateObject(“ADODB.Command”)
    dbConnection.ActiveConnection = Environment.Value(“ConnectionStr”)

    On Error Resume Next
    dbConnection.Open
    dbConnection.CommandType = 4
    dbConnection.CommandText = dbo.tp_pmt_inbound_process_ack
    dbConnection.Parameters.Refresh
    dbConnection.Parameters(1).Value = “@arg_ackmsgid=’89909′”
    dbConnection.Parameters(2).Value = “@arg_ackmsgtime=’2009-12-15T05:02:09′”
    dbConnection.Parameters(3).Value = “@arg_ackmsginitiator=’uhushf'”

    dbConnection.Execute()
    dbConnection.Close
    Set dbConnection= Nothing

    Can u please correct this ????
    Eagerly awaiting for ur reply.

    Thanks in advance
    Chethana

    Comment by Chethana | December 14, 2009 | Reply

  7. Hi

    In DB function; If return type is boolean, following error is received
    “Parameter object is improperly defined. Inconsistent or incomplete information was provided.”
    If return type is numeric problem does not occur, QTP script works. How can we handle a BOOLEAN type return?

    Thanks

    Comment by Gcetin | February 9, 2012 | Reply

  8. Hi, Can anyone please help me. I need a sample code on how to execute all the procedures within a package. Below is the code Ia m trying to use. This is just one procedure inside that package and there are 3 more. I am getting the error item cannot be found in the collection corresponding to the requested name or ordinal
    Function RunStoredProcedure(Basketball_Playerstat)
    sDatabaseName=”SDRTEST”
    sUID=”basketball”
    sPWD=”basketball”
    sStoredProcedure=”Basketball_Playerstat”
    Set cm = CreateObject(“ADODB.Command”)
    Set recObj=createobject(“adodb.recordset”)
    cm.ActiveConnection = “DSN=QTP;UID=basketball;PWD=basketball;DBQ=SDRTEST;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;”
    cm.CommandType = 4
    cm.CommandText = “Basketball_Playerstat”
    cm.Parameters.Refresh
    cm.Parameters(“in_event_id”).Value=”400238671″
    cm.Parameters(“in_league_id”).Value = “3180”
    cm.Parameters(“in_playerid”).Value = “2489030”
    cm.Parameters(“in_sourceid”).Value = “2”
    cm.Execute()
    Set cm = Nothing
    Set recObj = Nothing
    End Function
    RunStoredProcedure(Basketball_Playerstat)

    Comment by Meera | March 27, 2012 | 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: