Executing a Stored DB procedure - QTP

Stored procedures can be executed using the Command object. Care must be taken to properly define the required input and output parameters of the stored procedure. Consider the following stored procedure:

  1. Stored Procedure Name : Authentication
  2. Purpose : To Check Whether Given Person is Authorised or not
  3. Parameters
  1. Input :
    EMPID - INPUT (number(5,0)) - Employee ID
    Password - INPUT (varchar(10) - Password
    PRODCODE - INPUT (varchar(lO)) - Project Code to Login into
  2. Output - N/A

4. Return Value(s): number

  1. - Succesfully Authenticated
  2. - Invalid Password
  3. - Employee Not Associated to Given Project
  4. - Some Error Unable To Authenticate

Needed constants

ConstadVarChar = 200
Constadlnteger = 3
ConstadParamlnput = 1
ConstadParamlnputOutput = 3
ConstadParamOutput = 2
ConstadParamReturnValue = 4
s_UserID = 37362
s_Password = "Test"
s_ProjID = "Project 1"
'create the command object
Set adoCommand = CreateObjectC'ADOOB, Command")
With adoCommand
.ActiveConnection = adoCon
CommandTimeout = 30
CommandType = 4 'adCmdStoredProc
Name of the stored procedure
CommandText = "Authentication"
Add all the Parameters to the Command Object

In case of a return value from the function that return value parameter should be added before adding any other parameter

Set adoPrm =.CreateParameter("retvalue", adInteger, adParamReturnValue, 5)
.Parameters.AppendadoPrm
Set adoPrm =.CreateParameter(empid", adInteger, adParamlnput, 5, s_UserID)
.Parameters.AppendadoPrm
Set adoPrm =.CreateParameter("password", adVarChar, adParamlnput, 50, s_ Password)
.Parameters.AppendadoPrm
Set adoPrm =.CreateParameter("projid", adVarChar, adParamlnput, 50, s_ProjID)
.Parameters.AppendadoPrm
'Execute the Stored Procedure adoCmd.Execute
'Check the Return Value and Raise Error if Neccesary
Select Case adoCmd.ParametersO'retvalue").Value
Case 0
MsgBox "Succesfully Authenticated"
Case 1
MsgBox "Invalid Password"
Case 2
Msgbox "Enployee Not Associated to Given Project"
Case Else
Msgbox "Some Error Unable To Authenticate"
End Select
End With
Set adoCommand = Nothing

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

QTP Topics