Stored Procedures are pre-compiled piece of SQL code which allow reusability. They allow input as well as output parameters. Multiple statements can be executed within a stored procedure.
We will be working on the Employees table.
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FullName] [nvarchar](250) NOT NULL,
[DeptID] [int] NULL,
[Salary] [int] NULL,
[HireDate] [date] NULL,
[ManagerID] [int] NULL
) ;
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (1, 'Owens, Kristy', 1, 35000, '2018-01-22' , 3);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (2, 'Adams, Jennifer', 1, 55000, '2017-10-25' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (3, 'Smith, Brad', 1, 110000, '2015-02-02' , 7);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (4, 'Ford, Julia', 2, 75000, '2019-08-30' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (5, 'Lee, Tom', 2, 110000, '2018-10-11' , 7);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (6, 'Jones, David', 3, 85000, '2012-03-15' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (7, 'Miller, Bruce', 1, 100000, '2014-11-08' , NULL);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (9, 'Peters, Joe', 3, 11000, '2020-03-09' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (10, 'Joe, Alan', 3, 11500, '2020-03-09' , 5);
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (11, 'Clark, Kelly', 2, 11500, '2020-03-09' , 5);
Procedures can be created by using the CREATE PROCEDURE command as follows.
1 - CREATE PROCEDURE to select data from table Employees.
Naming standard - Prefix with usp to indicate user stored procedure.
Create Procedure usp_Test as
(
Select * from Employees
)
Executing a Stored Procedure -Stored Procedures can be executed using the exec keyword or simply by printing the stored procedure name.
exec usp_Test
usp_Test
2 - CREATE Procedure with input parameter -
Create Procedure usp_Test1 @Salary int as
(
Select * from Employees where Salary > @Salary
)
Executing stored procedure with parameters -
You can execute by passing parameter value in the below format.
Here we mention the parameter name and then assign it a value.
exec usp_Test1 @Salary = 35000
We can also execute by just passing the parameter value.
exec usp_Test1 3500
3 - Altering a Stored Procedure
You can use
ALTER PROCEDURE proc_name - This alters an existing stored procedure.
or CREATE or ALTER proc_name - This creates a new stored procedure if it does not already exist . If the stored procedure already exists, this modifies the existing stored procedure.
CREATE or ALTER Procedure usp_Test1 @Salary int = NULL as
(
Select * from Employees where Salary > @Salary
)
exec usp_Test1
exec usp_Test1 3500
exec usp_Test1 @Salary = 35000
Passing default values to the Stored Procedure.
In case of scenarios where it is possible that a stored procedure might be called without a parameter value, we can define a default parameter value.
In the above scenario, the stored procedure will be executed considering the default value of the parameters.
Create Procedure usp_Test2 @Salary int = NULL , @DeptID int = NULL as
(
Select * from Employees where Salary > @Salary
and DeptID = @DeptID
)
The below command will execute the stored procedure with values @Salary = NULL and @DeptID = NULL
exec usp_Test2
exec usp_Test2 @Salary = 35000 , @DeptID = 1
exec usp_Test2 35000 , 1
PROCEDURE with Output parameter.
When specifying an output parameter, the keyword OUTPUT should be included after the parameter name and datatype.
Create or Alter Procedure usp_Test2 @Salary int = NULL , @DeptID int = NULL
, @EMPID int OUTPUT as
(
Select @EMPID = count(EmployeeID) from Employees where Salary > @Salary
and DeptID = @DeptID
)
To execute and collect the output value from the parameter, we need to declare a variable to collect the value of the output variable while executing the procedure.
declare @EMPID int
exec usp_Test2 @Salary = 1000 , @DeptID = 1 , @EMPID = @EMPID OUTPUT
Select @EMPID
Turn of Messages - NOCOUNT
To turn off messages that indicate how many rows were affected by the last operation (ex - 4 rows affected ) in the SSMS, we can use the SET NOCOUNT ON option.
Create or Alter Procedure usp_Test2 @Salary int = NULL , @DeptID int = NULL
, @EMPID int OUTPUT as
SET NOCOUNT ON
Select @EMPID = count(EmployeeID) from Employees where Salary > @Salary
and DeptID = @DeptID
declare @EMPID int
exec usp_Test2 @Salary = 1000 , @DeptID = 1 , @EMPID = @EMPID OUTPUT
Select @EMPID
Lets now create a sample stored procedure to Insert the maximum hire date from the Employees table in the HireLog table. The date will be inserted only if it falls in the current month and year.
Create table HireLog
(LastHired date
)
Stored Procedure -
CREATE OR ALTER PROCEDURE uspHireDate as
Begin
declare @LastHired date
Set @LastHired = (Select Max(HireDate) from Employees)
IF Month(@LastHired) = Month(GetDate()) and Year(@LastHired) = Year(GetDate())
Insert into HireLog VALUES (@LastHired)
END
uspHireDate
Select * from HireLog