Stored Procedure In SQL Server

May 28, 2022 0 1.3K

Mustakim Khalifa

Mustakim Khalifa is a Software Engineer. He has experience in designing and developing web applications, ...

In this article, We will learn about the concept of Stored Procedures in SQL servers.

  • A stored procedure is a group of precompiled SQL statements into a logical unit.
  • It is stored as an object inside the database server. The SQL Database Server stores the stored procedures as named objects.
  • Each stored procedure in SQL Server always contains a name, parameter lists, and Transact-SQL statements.
  • Based on the statements in the procedure and the parameters you pass, it can perform one or multiple DML operations on the database, and return value.
  • It can support almost all relational database systems.
  • SQL Server builds an execution plan when the stored procedure is called the first time and stores them in the cache memory. The plan is reused by SQL Server in subsequent executions of the stored procedure, allowing it to run quickly and efficiently.
  • The main purpose of stored procedures is to hide direct SQL queries from the code and improve the performance of database operations such as SELECT, UPDATE, and DELETE data.

Types of Stored Procedures

There are two types of Stored Procedures:

  1. System Stored Procedures
  2. User-defined Stored Procedures

1. System Stored Procedures

  • The server’s administrative tasks depend primarily on system stored procedures.
  • When SQL Server is installed in the system that time it will create system procedures. The system stored procedures prevent the administrator from querying or modifying the system and database catalog tables directly.

How to list all System Stored Procedures:

SELECT *
FROM   sys.all_objects
WHERE  type = 'P' AND is_ms_shipped = 1

Output as follows,

2. User-defined Stored Procedures:

  • Developers or administrators build user-defined stored procedures and stored into Database.
  • These procedures contain one or more SQL statements for the SELECT, UPDATE, or DELETE data from the database.
  • A stored procedure may or may not accept input or output parameters. DDL and DML commands are used together in a user-defined procedure.

User-defined Stored Procedures can divide into further two types:

  1. T-SQL Stored Procedures
  2. CLR Stored Procedures

T-SQL Stored Procedures:

  • Transact-SQL procedures are the most popular types of SQL Server procedures.
  • It will take parameters and returns them. These stored procedures manage INSERT, UPDATE, and DELETE statements with or without parameters and output row data.

CLR Stored Procedures:

  • The SQL Server procedures are a group of SQL commands, and the CLR indicates the common language runtime.
  • CLR stored procedures are made up of the CLR and a stored procedure, which is written in a CLR-based language like VB.NET or C#.
  • CLR procedures are .Net objects that will run in the SQL Server database’s memory.

Syntax:

CREATE { PROCEDURE | PROC } [schema_name.]procedure_name
     [ @parameter [type_schema_name.] datatype 
     [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]
    ,@parameter [type_schema_name.] datatype
     [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] ]

[ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]
[ FOR REPLICATION ]

AS

BEGIN
   [declaration_section]

   Statement write here......

END

Here,

schema_name: It will indicate the name of the schema that owns the stored procedure.
procedure_name: It will indicate the name to assign to this procedure in SQL Server.
@parameter:  Here, it will indicate the number of parameters passed into the procedure.
type_schema_name: The schema name that owns the data type.
datatype: The data type for the @parameter.
VARYING: It is defined for cursor parameters when the result set is an output parameter.
default: if we want to assign the default value to @parameter.
OUT or OUTPUT: It indicates that the @parameter is an output parameter.
READONLY: It defined that the @parameter can not be overwritten by the stored procedure.
ENCRYPTION: It defined that the source for the stored procedure will not be stored as plain text in the system views in SQL Server.
RECOMPILE: It defined that a query plan will not be cached for this stored procedure.
EXECUTE AS clause: It will set the security context to execute the stored procedure.
FOR REPLICATION: It means that the stored procedure is executed only during replication.

Example :

First, We will see the Example of a Stored Procedure without a parameter.

For that First, we will create a table as follows,

CREATE TABLE tbl_Employee
(ID INT PRIMARY KEY IDENTITY(1,1),
EmplyeeName VARCHAR(50),
City VARCHAR(50),
Salary INT
)

Now, We will insert the records into the Table as follows,

INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Mustakim','Valsad',1000)
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Mihir','Surat',1000)
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Vibha','Navsari',1000)
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Priyank','Bardoli',1000)
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Satish','Chikhli',1000)
INSERT INTO dbo.tbl_Employee(EmplyeeName,City,Salary) VALUES('Mustakim','Valsad',1000)

A Record will be as follows,

Let’s create a Stored Procedure as follows,

CREATE PROCEDURE dbo.USP_Get_Employee_Data
As 
BEGIN
SET NOCOUNT ON;
SELECT e.EmployeeName,e.Salary,e.City
FROM dbo.tbl_Employee e with(Nolock);

END

Now, We will Execute above created Stored Procedure as follows,

EXECUTE dbo.USP_Get_Employee_Data
or
EXEC dbo.USP_Get_Employee_Data

After Execute above statement, the output is as follows,

SET NOCOUNT ON in Stored Procedure:

  • The SET NOCOUNT ON prevents the message that displays the number of rows affected by SQL queries from being shown.
  • NOCOUNT denotes that the count is turned off. It defined that if SET NOCOUNT ON is set, no message would appear indicating the number of rows affected by the statement.

Now, We will see the Example of a Stored Procedure with Input and Output Parameters:

CREATE or ALTER PROCEDURE USP_Get_Employee_Data
        @EmployeeId INT 
       ,@EmployeeName VARCHAR(50) OUTPUT
As 
BEGIN
SET NOCOUNT ON;

SELECT @EmployeeName=e.EmployeeName
FROM dbo.tbl_Employee e with(Nolock)
WHERE e.ID=@EmployeeId;

END

Here, @EmployeeId is the InputParameter and @EmnployeeName is the Output Parameter.

Now, we will execute above created Stored Procedure.

Here, we need to pass the Output parameter @EmployeeName as follows:

OUTPUT keyword is required to pass the output parameter.

DECLARE @EmployeeName VARCHAR(50)

EXECUTE dbo.USP_Get_Employee_Data  1,@EmployeeName OUTPUT 

SELECT @EmployeeName as 'Employee Name'

After Execute above statement, the output is as follows,

Steps for creating the Stored Procedure, If we are using the SSMS:

1. First, Select the Database -> Programmability -> Stored Procedures.

2. Then, Right-click on the Stored Procedures folder to open the menu and then select the Stored Procedure option as follows:

3. Last, When we select the Stored Procedure option, We will get a new query window containing the default Stored Procedure Template. So here, we can add the name of the procedure, the number of parameters, and the SQL query.

How to rename stored procedures in SQL Server?

  • SQL Server does not allow us to modify the name of a stored procedure. Because renaming of a stored procedure does not modify the name of the stored procedure in the sys.sql_modules. so, if we need to change done in the existing stored procedure, simply DROP and recreate it with a new name or changes.

Step to Modify Stored Procedure in SSMS as follows,

1. First, Navigate to the Database -> Programmability -> Stored Procedures.

2. Then, Expand the Stored Procedures folder, right-click on the stored procedure that you want to modify, and then select the Modify option as follows:

3. In Last, Once we click the Modify option, we will get a new window with an auto-generated ALTER PROCEDURE code. Here we can make changes as per our needs.

How to list all stored procedures in SQL Server?

SELECT * FROM sys.procedures;

It will display output as follows,

How to delete/drop stored procedures in SQL Server?

There are two ways to delete Stored Procedures:

  1. Using T-SQL Query
  2. Using SQL Server Management Studio

1. Using T-SQL Query

IF OBJECT_ID ('procedure_name', 'P') IS NOT NULL     
    DROP PROCEDURE procedure_name;

2. Using SQL Server Management Studio

Step for Drop Stored Procedure using SSMS as follows,

1. First, Go to the Database -> Programmability -> Stored Procedures.

2. Then, Expand the Stored Procedures folder, right-click on the stored procedure that you want to remove, and then select the Delete option as follows:

3. In Last, Once we click the Delete option, we will get a Delete Object window. Click OK to remove the stored procedure.

Temporary Stored Procedure

We can create temporary procedures in the same way as we are creating temporary tables. The tempdb database is used to create temporary procedures. Here, We can divide the temporary procedures into two types:

  1. Local Temporary Stored Procedures
  2. Global Temporary Stored Procedures.

Local Temporary Stored Procedures:

  • We can create Local Temporary Stored Procedure by using the # as a prefix and accessing only in the session in which they were created. When the connection is closed, this process is immediately terminated.

Here’s an example of how to create a local temporary procedure:

CREATE PROCEDURE #Temp  
AS  
BEGIN  
PRINT 'Local temp procedure'  
END

Global Temporary Stored Procedure:

  • We can create a Global Temporary Stored Procedure by using the ## as a prefix and accessed from any sessions. When the connection that was used to create the procedure is closed, this procedure is automatically terminated.

Here’s an example of how to create a global temporary procedure:

CREATE PROCEDURE ##TEMP  
AS  
BEGIN  
PRINT 'Global temp procedure'  
END

Advantages of Stored Procedure:

  • Reusable:  Multiple users and applications can easily reuse stored procedures by calling them.
  • Easy to modify: You can quickly change the statements in a stored procedure as and when you want, with the help of the ALTER PROCEDURE command.
  • Security: Stored procedures allow you to enhance the security of an application or a database by restricting the users from direct access to the table.
  • Low network traffic: The server only passes the stored procedure name instead of the whole SQL query, so it will reduce network traffic.
  • Increases performance: on the first use, a stored procedure is created and stored in the buffer pool for quick execution next time.

Disadvantages of Stored Procedure:

  • Debugging: Debugging a stored procedure is never easy, it is not advised to write and execute complex business logic using them. As a result, if we will not handle it properly, it can result in failure.
  • Dependency: As we know, professional database developers handle huge data sets in large organizations. And the application developers must depend on them because any minor changes must be referred to a database developer, who can fix bugs in existing procedures or build new ones.
  • Specific to a Vendor: Stored procedures written in one platform cannot be run on another. Since the store procedures written in Oracle are more difficult, we need to rewrite the entire store procedure for SQL Server.
  • Versioning: Version control is not supported in the stored procedure.

I hope, This article will help you to understand the concept of Store Procedure.

Thank you.

Mustakim Khalifa

Mustakim Khalifa is a Software Engineer. He has experience in designing and developing web applications, desktop applications, and web APIs. He has good skills in ASP.NET C#, ASP.NET Core, ASP.NET MVC, Web API, SQL, My SQL, ADO.NET, Entity Framework, HTML, JavaScript, jQuery, and CSS.
Read More

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories