MS SQL Stored Procedure Example

In this tutorial you will learn how to create a stored procedure in sql server database, stored procedure is often termed as sql sp by database developer.

Stored procedure is a database object in sql Server, A stored procedure is a set of sql statements with some business logic written inside, compiled and stored in the database.

Stored procedure is pre compiled and type safe, work faster than normal query during runtime. Stored procedure is a reusable object can be called from different places with parameters.

Stored procedure characteristic

There are two types of Stored procedure in SQL Server, User defined store procedure and System store procedure

Why to use SET NOCOUNT ON in stored procedure?
When we write SET NOCOUNT ON it means it will not return the messages of the number of rows affected.

In this mysql stored procedure tutorial you will learn following things.

• How to create stored procedure
• SET NOCOUNT ON
• How to pass input parameters and output parameters etc
• How to alter and drop stored procedure
• If else statement in stored procedure
• Case statement in stored procedure
• Writing loop in stored procedure
• How to handle exception in stored procedures

User defined Stored procedure is the mixture of DDL (Data Definition Language) and DML (Data Manipulation Language ) commands, We can write both type of statment in Store procedures

Now we see how to Create A Stored Procedure In SQL Server Management Studio

Stored procedure example in sql

In this stored procedure, I have one simple select statement with order by clause, and there is no input parameter.

You can see i have used SET NOCOUNT ON; and begin end block.

SET NOCOUNT ON Indicates that it should not return any other statement, other than speechified select statement in my code.

CREATE PROCEDURE usp_GetStudents
AS
BEGIN
    SET NOCOUNT ON;
    
    Select * from tbStudent order by Firstname;
     
END
GO

Now here in below stored procedure, I want the record one particular student.

CREATE PROCEDURE [dbo].[usp_GetStudent]
	-- Add the parameters for the stored procedure here
	@StuId int	
AS
BEGIN
	SET NOCOUNT ON;
    -- Insert statements for procedure here
	SELECT * from tbStudent where StuId= @StuId
END

This is stored procedure with if else condition, if student id 0, then it will be considered as new student, and add in database, else it will update the student information

Create PROCEDURE [dbo].[spAdUpdateStudent]
	@StuId int,
	@fname varchar(50),
	@lname varchar(50),
	@email varchar(50),
	@Mobile varchar(50)	
AS
BEGIN
	SET NOCOUNT ON;
	if(@StuId=0)
		begin
			insert into tbStudent 
			(Firstname,Lastname, email, ContactNumber)
			values
			(@fname,@lname,@email,@Mobile)				 
		end
	else 
	  begin
		  Update tbStudent 
				set email=@email,
				Firstname=@fname,
				Lastname=@lname,
				ContactNumber=@Mobile
		 where StuId= @StuId
	  end
END

• Input parameters

  Stored procedures can have any number of input parameters, parameters can be optional or mandatory

  In above example studentId is mandatory input parameter

  CREATE PROCEDURE usp_UpdateDetails
      @studentId bigint
  
  

• Declare variable

  To declare a variable you need to start with declare keyword, then variable name and data type

  declare @currentStudentId bigint
  

• Assigning a value to variable

  Like in above stored procedure i have assigned a value to variable currentStudentId.

  select @currentStudentId=StudentId from tbStudent
  where StudentId=@studentId
  

To alter the stored procedure, you first need to open it either from object explorer as showed in above picture, or using query command sp_helptext "spName"

After opening the code, use Alter keyword and make whatever changes you want.

Alter PROCEDURE usp_GetStudents
AS
BEGIN
    SET NOCOUNT ON;
    
    Select * from tbStudent order by Firstname;
     
END
GO

To drop a stored procedure in ms sql server you need to use drop keyword like Drop PROCEDURE procName, remember drop means permanent deletion of the object.

Drop PROCEDURE usp_GetStudents

Loop in Stored Procedure

We can write any loop in cursor, but in real-time scenario to meet the business requirement we write cursor to loop through collection and doing some manipulation, then save that in stores procedure, here is an example of how to write cursor in stored procedure.

We also can write while loop in stored procedure like example below.

Create PROCEDURE [dbo].[usp_CreateMasterData]
AS
BEGIN
SET NOCOUNT ON;
truncate table [tbWeek];
declare @n int =1;
WHILE @n <= 52
	BEGIN
	   INSERT INTO [tbWeek]
		(WeekName, UpdateDate)	
		VALUES 
	   (CONCAT('Week ', @n),  GETDATE());
	   SELECT @n = @n + 1
	END
END

Now call the Stored procedure with Parameter value, if you don't provide parameter value this will produce error.

exec usp_UpdateDetails 2

Here are few points to remember while writing a stored procedure!

• If updating / inserting / deleting from more than one table, must use transaction
• In stored procedure, you must write try catch block to implement error handling
• Never write multiple functionalities into one stored procedure, rather break the functionalities into multiple stored procedure, maintain Single Responsibility Principle

Stored procedure and functions

We can call any UDF (user defined function) from stored procedure, there are difference between stored procedures and functions, learn more about UDF (user defined function)

Can I call a stored procedure within a stored procedure?
Yes, Stored procedure can be executed from anywhere

Can I call a Trigger within a stored procedure?
No, Trigger gets fired based on event on table, learn more about Trigger

Return Multiple Recrodsets using stored procedure Example
Below example will return details from tbStudent and tbTeacher table

create procedure usp_getStudentsTeachers
as
begin
set nocount on
    Select fullname, address, contactnumber from dbo.tbStudent
    Select fullname, address, contactnumber from dbo.tbTeacher
end

You may be interested to know how stored procedure works in MySQL server.

You should also read how to read multiple recrodset using DataReader in Ado.Net