Feb 5, 2013

Stored procedure parameters in sql server


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