How to pass parameters in sql
server? What is input or output parameters. How to pass optional parameters,
table variables, cursors etc? to execute
stored procedure. In this post we will discuss all above topics.
There are two types of parameters
in sql server:
a. Input parameter
b. Output parameter
Sql
server procedure input parameter:
When a value is passed to stored
procedure is called input parameter. By default all the parameters of stored
procedure are input parameter. I am explaining it by very simple example:
--Creating a stored procedure with three input parameters
CREATE PROC uspGetParams(
@ntRollNo AS
BIGINT,
@vcName AS VARCHAR(100),
@vcPhone VARCHAR(15)
)
AS
BEGIN
SELECT
@ntRollNo AS
[Roll Number],
@vcName AS
Name,
@vcPhone AS
Phone
END
To execute this stored procedure we
must have to pass three parameters. For example:
EXECUTE dbo.uspGetParams
50,'Scott','80808080'
In this way we will have to pass
the value to parameters in correct order. If we will specify the name of parameters
while executing it then order of parameters doesn't matter. For example:
--With order
EXECUTE dbo.uspGetParams
@ntRollNo =
50,
@vcName = 'Scott',
@vcPhone = '80808080'
--Without order
EXECUTE dbo.uspGetParams
@vcName =
'Scott',
@vcPhone = '80808080',
@ntRollNo =
50
Sql
server procedure output parameter:
When value is gotten from parameter
of stored procedure is called output parameter. To make any parameters as
output parameter we must have to specify it by keyword OUT or OUTPUT while
creating the procedure. I am explaining it by very simple example:
--Creating stored procedure with three input parameters
-- and two output parameters.
CREATE PROC uspSendStatus(
@ntRollNo AS
BIGINT, --Input parameter
@vcName AS VARCHAR(100), --Input parameter
@vcPhone VARCHAR(15), --Input parameter
@btStatus AS
BIT OUTPUT, --Output parameter
@vcStatusMsg AS
VARCHAR(MAX) OUTPUT --Output parameter
)
AS
BEGIN
SELECT
@ntRollNo AS
[Roll Number],
@vcName AS
Name,
@vcPhone AS
Phone
SET
@btStatus = 1
SET
@vcStatusMsg = 'Success'
END
Note: It is no necessary to declare
the output parameters after the input parameters but it is good practice.
To execute it we will have to
declare local variables to get the values from stored procedure. For example:
DECLARE @btStatus AS BIT
DECLARE @vcStatusMsg AS
VARCHAR(MAX)
EXECUTE dbo.uspSendStatus
50,
'Scott',
'80808080',
@btStatus OUTPUT,
@vcStatusMsg OUTPUT
SELECT
@btStatus AS
btStatus,
@vcStatusMsg AS
vcStatusMsg
GO
If will specify the column name the
order of parameters doesn't matters. For example:
DECLARE @btStatus AS BIT
DECLARE @vcStatusMsg AS
VARCHAR(MAX)
--With order
EXECUTE dbo.uspSendStatus
@ntRollNo =
50,
@vcName = 'Scott',
@vcPhone = '80808080',
@btStatus =
@btStatus OUTPUT,
@vcStatusMsg =
@vcStatusMsg OUTPUT
SELECT
@btStatus AS
btStatus,
@vcStatusMsg AS
vcStatusMsg
GO
--It is not necessary that local variables has same name as output
parameters.
DECLARE
@btStatusLocal AS BIT
DECLARE @vcStatusMsgLocal AS
VARCHAR(MAX)
--Without order
EXECUTE dbo.uspSendStatus
@btStatus =
@btStatusLocal OUTPUT,
@vcName = 'Scott',
@vcPhone = '80808080',
@vcStatusMsg =
@vcStatusMsgLocal OUTPUT,
@ntRollNo =
50
SELECT
@btStatusLocal AS
btStatus,
@vcStatusMsgLocal AS
vcStatusMsg
Note: If we passed the value to the
parameters of stored procedure by specifying parameter name then it is
necessary to specify for all parameters. For example:
DECLARE @btStatus AS BIT
DECLARE @vcStatusMsg AS
VARCHAR(MAX)
--It is incorrect to write
EXECUTE dbo.uspSendStatus
@ntRollNo =
50,
'Scott',
'80808080',
@btStatus OUTPUT,
@vcStatusMsg =
@vcStatusMsg OUTPUT
SELECT
@btStatus AS
btStatus,
@vcStatusMsg AS
vcStatusMsg
We will get error message: Must
pass parameter number 2 and subsequent parameters as '@name = value'. After the
form '@name = value' has been used, all subsequent parameters must be passed in
the form '@name = value'.
Sql
server stored procedure with optional parameter:
Some time we need to pass some
default value to the parameters. So if we don't pass value to the parameter it
will use its default value otherwise it will use the value which has been passed
to it. Such parameters are called option parameter. For example:
--This procedure has two compulsory and one optional parameter
CREATE PROC uspGetOptionValue(
@ntRollNo AS
BIGINT,
@vcName AS VARCHAR(100),
@vcPhone VARCHAR(15) = '90909090' --Optional params
)
AS
BEGIN
SELECT
@ntRollNo AS
[Roll Number],
@vcName AS
Name,
@vcPhone AS
Phone
END
To execute it:
We are not passing any value to the
optional parameter. So it will use default value.
EXECUTE dbo.uspGetOptionValue 100,'Greg'
Output:
Roll Number
|
Name
|
Phone
|
100
|
Greg
|
90909090
|
We are passing a value to the
optional parameter. So it will use passed value.
EXECUTE dbo.uspGetOptionValue 100,'Greg','80808080'
Output:
Roll Number
|
Name
|
Phone
|
100
|
Greg
|
80808080
|
We can also pass the value to the parameters
by specifying parameters name. If we
have not declared the optional parameters at end then it is necessary to
execute it by specifying name of parameters in case of we are not passing any
value to the optional parameters for example:
EXECUTE dbo.uspGetOptionValue
@ntRollNo =
50,
@vcName = 'Scott'
Passing
the table variable as a parameter of stored procedure in sql server:
--Creating the table type for table variable
CREATE TYPE StudentType AS TABLE(
RollNo INT,
Name VARCHAR(30)
)
GO
--Creating procedure with parameters of type StudentType.
--Table type parameters must be readonly
CREATE PROCEDURE GetInfo(
@StudentData StudentType READONLY
)
AS
BEGIN
SELECT *
FROM
@StudentData
END
GO
--Declaring the table variable @Student
DEClARE @Student AS StudentType
--Inserting some records into it
INSERT INTO @Student
VALUES(10,'Scott'),(11,'Greg')
--Passing the table variable as a parameter to execute procedure
EXECUTE GetInfo @Student
Note: In sql server there is not
any concept of array. We can treat the table variable as array.
Passing
the cursor variable as an output parameter of stored procedure in sql server:
For example:
--Creating a table
CREATE TABLE tblEmp(
ntEmpID
BIGINT IDENTITY
PRIMARY KEY,
vcName
VARCHAR(100)
)
--Inserting some records into it
INSERT tblEmp VALUES('Scott'),('Greg')
GO
--Creating procedure with output parameter as cursor variable
CREATE PROCEDURE GetEmpInfo(
@Emp_Id_Cur CURSOR
VARYING OUTPUT
)
AS
BEGIN
SET
@Emp_Id_Cur = CURSOR
FOR
SELECT TOP(3) ntEmpID FROM tblEmp
OPEN
@Emp_Id_Cur
END
GO
--Declaring cursor variable
DECLARE
@Emp_Id_Cur CURSOR
DECLARE @ntEmpID INTEGER
--Executing the procedure to get the value of cursor variable
EXECUTE GetEmpInfo @Emp_Id_Cur OUTPUT
--Getting recodes from table using cursor variable
FETCH
@Emp_Id_Cur INTO @ntEmpID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM tblEmp WHERE ntEmpID =
@ntEmpID
FETCH NEXT FROM @Emp_Id_Cur
INTO @ntEmpID
No comments:
Post a Comment