SQL Store Procedure Part-1

When you have to re-write a SQL query multiple times, it’ll be better to create a store procedure for that query. It’ll save your time and you can re-use the store procedure as much as you can. Because when you create a store procedure it’ll be saved into your Database under Programmability. Later just call it to execute it.

Definition: Store procedure is a group of T-SQL statements with an assigned name, which are stored in a relational DBMS as a group. Store procedure is a Trigger.

Suppose we have a Students table contains Id, Name, Gender, and Department. Let’s create a store procedure with parameters.

CREATE PROCEDURE spGetStudentsById
@Id int,
@Department nvarchar(20)
AS
BEGIN
    SELECT Name, Gender, Department from Students
    WHERE Id = @Id AND Department = @Department
END

The name convention of any store procedure should be like spStoreProcedureName. Now, we created a store procedure named spGetStudentsById which selects information from Students table.

For executing a SP just run EXECUTE spGetStudentsById.

EXECUTE spGetStudentsById 1, ‘CSE’

It’ll gather information from Students table where Id=1 and Department=’CSE’

Updating a Store Procedure: We have to use ALTER keyword for updating a SP. For example, we’re going to select data from Students table order by Name.

ALTER PROCEDURE spGetStudentsById
AS
BEGIN
    SELECT Name, Gender, Department FROM Students ORDER BY Name
END

Then run EXECUTE spGetStudentsById to see the output of this SP.

Shortcut to see what query is written into the SP. MSSQL has a build-in keyword named sp_helptext.

sp_helptext spGetStudentsById

Encrypting a SP: If you defined a SP as encrypted then no one can see what query is written inside the SP.

ALTER PROCEDURE spGetStudentsById
WITH ENCRYPTION
AS
BEGIN
    SELECT Name, Gender, Department FROM Students ORDER BY Name
END