Sunday, October 12, 2014

How to Create Parameterized Stored Procedure: SQL

8:21 AMSunday, October 12, 2014
At times, you need to execute a procedure for different values of a variable that are provided at run time. For this, you can create a parameterized stored procedure. Parameters are used to pass values to the stored procedure during run time. These values can be passed by using standard variables.

The parameter that passes the values is defined as input parameter. A stored procedure has the capability of using a maximum of 2100 parameters. Each parameter has a name, data type, direction, and a default value.

The following example creates a stored procedure displaying the employee ID, the login ID, and title of the employees that have the same title provided as an input during


CREATE PROC prcListEmployee @title char (50)
PRINT ‘List of Employees’
SELECT EmployeeID, LoginID, Title
FROM HumanResources.Employee
WHERE Title = @title

Execute the stored procedure, prcListEmployee, by using the following statement:

EXECUTE prcListEmployee ‘Tool Designer’

While executing the stored procedures, you can also provide the values for the parameters by explicity specifying the name and value of the parameter. In the previous example, you can also pass the parameter value by using the name of variable, as shown in the following SQL statement:

EXECUTE prcListEmployee @title = ‘Tool Designer’
Protected by Copyscape Online Copyright Protection Software


Post a Comment

Toggle Footer