SQL Server stored procedure tutorial

This SQL Server tutorial, we will discuss what is stored procedure in SQL server? How to create a store procedure in the SQL server? And also we will see various SQL server stored procedure examples:

  • What is a stored procedure?
  • How to create a stored procedure in SQL server?
  • Syntax to execute a stored procedure in SQL Server
  • SQL Server Stored procedure example
  • Create a Stored procedure from SQL Server management studio
  • Delete all stored procedures at once in SQL Server database
  • Get stored procedure source text in SQL server
  • Single stored procedure to insert update and delete operation in sql server
  • Stored procedure to update record in SQL Server Table
  • SQL Query to get all tables and stored procedures in sql server
  • List all Stored Procedure Created and Modified in Last N Days in SQL Server
  • How to handle exception in stored procedure in sql server?

What is a stored procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.

Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version.

Stored procedures reduce network traffic and improve performance.

Stored procedures can be used to help ensure the integrity of the database.

The compilation step is required only once when the stored procedure is created. After that, it does not require recompilation before executing unless it is modified.

A stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure.

This tutorial is a part of SQL Server tutorial series:

How to create a stored procedure in SQL server?

Below is the syntax to create/modify/execute a stored procedure in sql server.

Syntax to create stored procedure in sql server

To Create:
 CREATE PROCEDURE StoredProcedureName
 AS
 SELECT * FROM Employee
 GO

Syntax to Modify stored procedure in sql server

ALTER PROCEDURE StoredProcedureName AS

Syntax to execute stored procedure in SQL Server

Below is the syntax to execute stored procedure in sql server.

EXEC "StoredProcedureName"

Below is the syntax to execute a stored procedure with parameter in sql server.

EXEC StoredProcedureName @CParameterName ="value"

SQL Server Stored procedure example

Below is a stored procedure that will take one input parameter and output parameter.

Create procedure GetEmployeeName
 (
 @Empid int,
 @EmpName varchar(200) out
 )
 as
 begin
 select @EmpName=FirstName+' '+LastName from EmloyeeDetail where @Empid=Empid
 end
 go

Below is the way to execute the above stored procedure:

declare @Name varchar(50)
 exec GetEmployeeName 3 ,@Name output
 select @Name

Create a Stored procedure from SQL Server management studio

To create a stored procedure from SQL Server management studio,
Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.

Right-click Stored Procedures, and then click New Stored Procedure.

Delete all stored procedures at once in SQL Server database

Now, we will see how to delete all stored procedures at once in SQL Server database. Their different approach to delete all stored procedures in SQL Server.

Approach-1:

First run the below sql query in sql server management studio.

SELECT 'DROP PROCEDURE ' + p.NAME
FROM sys.procedures p

The above command will generate the DROP PROCEDURE statement like below:

Delete all stored procedures at once in SQL Server

And then you can copy those statements and put it in a new query window and run the commands.

Approach-2:

Directly run the below sql query in the sql server management studio.

DECLARE @procedureName varchar(500)
DECLARE cur CURSOR FOR SELECT [name] FROM sys.objects WHERE type = 'p' OPEN cur
FETCH NEXT FROM cur INTO @procedureName
WHILE @@fetch_status = 0
BEGIN EXEC('DROP PROCEDURE ' + @procedureName)
FETCH NEXT FROM cur INTO @procedureName
END CLOSE cur
DEALLOCATE cur

This is how we can delete all stored procedures at once in SQL Server database.

Get stored procedure source text in SQL server

Now, we will see how to get stored procedure text in SQL server

Recently I got a situation where I do not have permission to create or modify the stored procedure, so I can not right click and click on modify to see the content of the stored procedure. But I need to check the stored procedure content. So I was looking for a query to get the content of a stored procedure.

Below is the query:

Syntax:

SELECT object_definition(object_id('Stored Proccedure Name')) AS [Object Definition];

Example:

SELECT object_definition(object_id('UserMaster_select')) AS StoredProcedureDetails

The above Select statement will return stored procedure as a text.

Single stored procedure to insert update and delete operation in sql server

Now, we will see how to write a single stored procedure that will insert, update and delete operations in the table in Sql server.

Below are steps for performing the common stored procedure in sql server.

Right click on a stored procedure, then add a new stored procedure. Here the stored procedure will take 5 parameters.

Based on the @action parameter we will insert, update or delete the record into the sql server table.

Create procedure sp_InserUpdateDelete
        @id integer;
        @name varchar(20);
        @add varchar(20);
        @img varchar(20);
        @action varchar(20)
As
      if(@action='I')
begin
     Insert into emp_master(emp_name,emp_address,emp_image) value (@name,@add,@img)
end
    if(@action='U')
begin
     Update emp_master set emp_name=@name,emp_address=@add,emp_image=@img where emp_id=@id
end
      if(@action='D')
begin
       Delete from emp_master where emp_id=@id
end
Return

Then save the stored procedure by pressing CTRL+S where emp_master is the table name, sp_InserUpdateDelete is the procedure name.

SQL Server stored procedure tutorial

Stored procedure to update record in SQL Server Table

Now, we will see how to write a stored procedure that will update a record in the SQL server table.

Below are the steps for performing update operation in SQL server. This procedure will take 4 input parameters.

Right click on a stored procedure, then add new stored procedure.

Create procedure sp_Update
@id integer,
@name varchar(20),
@add varchar(20),
@img varchar(20)
  As
     Update Emp_master set  emp_name=@name,emp_address=@add,emp_image=@img where emp_id=@id
Return

Here Table Name is Emp_master and Procedure name is sp_Update. Then Save the stored procedure.

SQL Query to get all tables and stored procedures in sql server

Now, we will see how to write a query to get all tables and stored procedures in sql server database.

Below is the sql query to get all the tables from a database in sql server.

Use TestDB
SELECT NAME AS ‘List OF Tables’ FROM SYS.TABLES

The result will be shown as shown in the figure below:

Get all tables from sql server

Below is the sql query to get all the stored procedures from a sql server database:

Use TestDB
SELECT NAME AS ‘List OF Stored Procedures’ FROM SYS.PROCEDURES

The result will be shown as shown in the figure below:

Get all stored procedures in sql server

List all Stored Procedure Created and Modified in Last N Days in SQL Server

We will discuss now, how to retrieve all the stored procedures created and modified in last N days in SQL Server.

Below is the query that will give us all the SQL server stored procedures that have been created in the last 30 days:

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 30

Below is the query that will give us all the stored procedures that have been modified in the last 30 days:

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 30

This is how we can retrieve all the stored procedures created and modified in the last N days in SQL Server.

How to handle exception in stored procedure in sql server?

Now, we will see how to use try-catch block in SQL Server stored procedure.

Below is the try-catch syntax for a sql server stored procedure

BEGIN TRY
     SQL Statement;
END TRY
BEGIN CATCH
    SQL Statement;
END CATCH

In the catch block you can retrieve error information by using built-in functions:

  • ERROR_NUMBER() returns the number of the error.
  • ERROR_SEVERITY() returns the severity.
  • ERROR_STATE() returns the error state number.
  • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE() returns the line number inside the routine that caused the error.
  • ERROR_MESSAGE() returns the complete text of the error message.

Example:

Alter PROCEDURE TestStoreProc
AS
GO
BEGIN TRY
     SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage
        ,ERROR_LINE() As LineNumber;
END CATCH;

If you will run the above stored procedure then you will see an error message like below:

How to handle exception in stored procedure in sql server

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

Now, we will see how to fix error Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.

Actually, I was using a stored procedure and within the stored procedure TRANSACTION statements were there like below:

USE [TestDB] GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Employees] @Name nvarchar (256) ,
@Age Int

AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION

–*************************************
INSERT INTO Employees (Name,Age) VALUES ( @Name, @Age )
–*************************************
COMMIT TRANSACTION
RETURN 1
— Sucessful
END TRY
BEGIN CATCH
RETURN -111
END CATCH
END

GO

Follow the below solution.

I found few things like you can remove transaction statements from the stored procedure if it is not necessary. But if you want to keep the transaction statements then you can try writing the below statements above CREATE PROCEDURE [dbo].[Employees] line.

SET XACT_ABORT ON
GO

So your stored procedure should look like below now:

USE [TestDB] GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET XACT_ABORT ON
GO

CREATE PROCEDURE [dbo].[Employees] @Name nvarchar (256) ,
@Age Int
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION

–*************************************
INSERT INTO Employees (Name,Age) VALUES ( @Name, @Age )
–*************************************
COMMIT TRANSACTION
RETURN 1
— Sucessful
END TRY
BEGIN CATCH
RETURN -111
END CATCH
END
GO

This is how we can fix error, Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.

You may like following sql server tutorials:

This sql server stored procedure tutorial, we learned the below things:

  • What is a stored procedure?
  • How to create a stored procedure in SQL server?
  • Syntax to execute stored procedure in SQL Server
  • SQL Server Stored procedure example
  • Create a Stored procedure from SQL Server management studio
  • Delete all stored procedures at once in SQL Server database
  • Get stored procedure source text in SQL server
  • Single stored procedure to insert update and delete operation in sql server
  • Stored procedure to update record in SQL Server Table
  • SQL Query to get all tables and stored procedures in sql server
  • List all Stored Procedure Created and Modified in Last N Days in SQL Server
  • How to handle exception in stored procedure in sql server?
Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SharePointSky.com

>