SQL Server Stored Procedures with Parameter - How to create it

In previous post, we have learnt about how to create a stored procedure without parameter. In this post, we will learn how to create stored procedure with parameters.

Parameters in a stored procedure

You can define a stored procedure with parameters. There are two types of parameters in a stored procedure - Input parameter and output parameter.

• During execution of stored procedure value of input parameter is passed.
• During execution of stored procedure value of output parameter is stored in a locally declared variable.

Stored Procedure with Input Parameter

Let us consider Employees table. Given the Id of employee, find the name of the employee. We create a stored procedure in this regards.

Use AppliedkDb
Go
CREATE TABLE Employees
(
 Id int
 ,first_name varchar(10)
 ,last_name  varchar(10)
)
Go

INSERT INTO Employees VALUES
      (1, 'Amit', 'Kumar')
     ,(2, 'Ajay', 'Raj')
     ,(3, 'Ritu', 'Sinha')
Go

Now we create stored procedure with input parameter. The stored procedure will be executed by passing employee id. In the following procedure, before the AS keyword, we use all the parameters of the procedure. The values of these parameters must be passed when the procedure is executed. After the AS keyword, we declare all the variables which are used in the procedure.

CREATE PROCEDURE usp_getfullname
@emp_id as int
AS
declare @fullname as nvarchar(50)
BEGIN
select @fullname = first_name + ' '+ last_name
from Employees
where Id = @emp_id
print @fullname
END

Now we execute the procedure using EXECUTE statement as given below. Note that id=2 is passed as parameter.

EXEC usp_getfullname 2

Note that more than one parameters are separated by comma during execution.

Stored Procedure with Input Parameter having Default Value

The following example illustrates how to use default value for input parameter of stored procedure. This default value is used when user does not provide value of the parameter.

CREATE PROCEDURE usp_getfullname_Default
@emp_id as int = 1 –- default value is 1
AS
declare @fullname as nvarchar(50)
BEGIN
select @fullname = first_name + ' '+ last_name
from Employees
where Id = @emp_id
print @fullname
END
GO

EXEC usp_getfullname_Default

The following example illustrates how to use output parameter of stored procedure. This value of output parameter is stored in a variable. It is necessary to declare a variable for this purpose when the stored procedure is executed.

Stored Procedure with Output Parameter

Use AppliedkDb
Go

CREATE PROCEDURE uspGetfullname
@emp_id as int,
@fullname as nvarchar(50) OUTPUT -- output parameter
AS
BEGIN
SELECT @fullname = first_name + ' '+ last_name
FROM Employees
WHERE Id = @emp_id
PRINT @fullname
END
GO

DECLARE @full_name as nvarchar(50)
EXEC uspGetfullname 2, @full_name output

Note that parameters are separated by comma during execution. If procedure has output parameter then we must declare variable to store the value of the output parameter.

In the next post, we will learn about Stored Procedure with Return statement.