In this tutorial you will learn how to create a stored procedure in SQL server database.
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.
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
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
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
To declare a variable you need to start with declare keyword, then variable name and data type
declare @currentStudentId bigint
Like in above stored procedure i have assigned a value to variable
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
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
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 store procedure
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