Free Web Development Online Tutorials, Learn to Code
SQL Stored procedures tutorial, create sql stored procedure return value, execute a sql stored procedure

MS SQL Stored Procedure Example

In this tutorial you will learn how to create a stored procedure in SQL server database.

What is Stored procedure in SQL?

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.

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

Write Store Procedures in SQL Server

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

create stored procedure in sql database

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
Stored procedure with if else condition

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
Things to learn from above SP example
  • 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
    
Alter Stored Procedure

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
Drop Stored Procedure

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

Execute a sql stored procedure

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 store procedure

  • If updating / inserting / deleting from more than one table, must use transaction
  • In stored procedure 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

stored procedures and triggers

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

Stored Procedure Naming Best Practice
You should not name any user-defined stored procedure with “sp_” prefix, this will indicate SQL server to search the stored procedure in master database, because all system defined stored procedure are prefixed with “sp_” , as a result this may slow down the performance.

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

Comment
Name
Email
Website
Subscribe
 
MS SQL Stored procedure Example

SQL job Interview Questions Answers

Course in Demand
Learn Stored procedure in SQL
MS SQL Examples | Join MS SQL Course