Stored Procedures in T-SQL

Hi All,

Hope you all are doing good.

In this article, we will know about stored procedures in T-SQL.
A stored procedure is group of T-SQL (Transact SQL) statements. 

If you have a situation, where you write the same query over and over again, you can save that specific query as a stored procedure and call it just by it's name.

Create Stored Procedure
Create Procedure Procedure_Name
AS
Begin

  -- your statements here

End

Note: As per Microsoft standards, it is not recommended to use "sp_" as a prefix. Because that was already given for system stored procedures.

Execute the stored procedure
To execute any stored procedure, type any of below and press F5 in keyboard.

• Stored_Procedure_Name 
• EXEC Stored_Procedure_Name 
• EXECUTE Stored_Procedure_Name

Note: You can also right click on the procedure name, in object explorer in SQL Server Management Studio and select EXECUTE STORED PROCEDURE.

Example for creating a stored procedure with input parameters: 
Create Procedure spGetEmployeesByGenderAndDepartment 
@Gender nvarchar(50),
@DepartmentId int
as
Begin

    Select Name, Gender from tblEmployee Where Gender = @Gender and 
    DepartmentId =  @DepartmentId

End

To execute above procedure, use either of below statements.
EXECUTE spGetEmployeesByGenderAndDepartment 'Male', 1
(OR)
EXECUTE spGetEmployeesByGenderAndDepartment @DepartmentId=1, @Gender = 'Male'

To view the text, of the stored procedure
Use system stored procedure sp_helptext 'SPName'


Example for changing the stored procedure:
Alter Procedure spGetEmployeesByGenderAndDepartment 
@Gender nvarchar(50),
@DepartmentId int
as
Begin

  Select Name, Gender from tblEmployee Where Gender = @Gender and 
  DepartmentId = @DepartmentId order by Name

End

Example for encrypting stored procedure
To encrypt the text of the SP, use WITH ENCRYPTION option. Once, encrypted, you cannot view the text of the procedure, using sp_helptext system stored procedure.
Alter Procedure spGetEmployeesByGenderAndDepartment 
@Gender nvarchar(50),
@DepartmentId int
WITH ENCRYPTION
as
Begin

    Select Name, Gender from tblEmployee Where Gender = @Gender and 
    DepartmentId = @DepartmentId

End

Delete Stored Procedure
To delete the SP, use either of below statements
DROP PROC 'SPName' 
or 
DROP PROCEDURE 'SPName'

Stored Procedure with output parameter: To create an SP with output parameter, we use the keywords OUT or OUTPUT.
Example: 
Create Procedure spGetEmployeeCountByGender
@Gender nvarchar(20),
@EmployeeCount int Output
as
Begin

 Select @EmployeeCount = COUNT(Id) 
 from tblEmployee 
 where Gender = @Gender

End

To execute above procedure, please use either of below statements.
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Female', @EmployeeTotal output
Print @EmployeeTotal
(or)
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender @EmployeeCount = @EmployeeTotal OUT, @Gender = 'Male'

Print @EmployeeTotal

Stored Procedure with return variable: To create an SP with return variable, we use the keywords return.
Example:  Create Procedure spGetTotalCountOfEmployees2
as
Begin
 return (Select COUNT(ID) from Employees)
End

To execute above procedure, please use either of below statements.
Declare @TotalEmployees int
Execute @TotalEmployees = spGetTotalCountOfEmployees2
Select @TotalEmployees

Difference between return values and output parameters

Return Status Value	Output Parameters
return only integer type	return any data type
return only one value	return more than one value
used to convery success or failure	used to return values like name, count ... etc

Below are the some of the useful system stored procedures with their usage.

sp_help SP_Name	View the information about the stored procedure, like parameter names, their datatypes etc. sp_help can be used with any database object, like tables, views, SP's, triggers etc. Alternatively, you can also press ALT+F1, when the name of the object is highlighted.
sp_helptext SP_Name	View the Text of the stored procedure
sp_depends SP_Name	View the dependencies of the stored procedure. This system SP is very useful, especially if you want to check, if there are any stored procedures that are referencing a table that you are about to drop. sp_depends can also be used with other database objects like table etc.

Advantages of stored procedures:

• Execution plan retention and reusability - Stored Procedures are compiled and their execution plan is cached and used again, when the same SP is executed again.
• Reduces network traffic - You only need to send, EXECUTE SP_Name statement, over the network, instead of whole SQL code.
• Code reusability and better maintainability - A stored procedure can be reused with multiple applications. If the logic has to change, we only have one place to change. So it provides better maintainability.
•  Better Security - A database user can be granted access to an SP and prevent them from executing direct "select" statements against a table.  This is fine grain access control which will help control what data a user has access to.
• Avoids SQL Injection attack - SP's prevent sql injection attack.

Thanks for reading my article.