What is Stored procedure?
Store procedure is a database object in SQL Server, A store procedure is a set of sql statements with some business logic written inside, compiled and stored in the database.
Store procedure is pre compiled and type safe, work faster than normal query during runtime.
Store 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.
User defined store 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 store procedure we write one select and one update statement, and the sp will have one mandatory parameter called studentId
CREATE PROCEDURE usp_UpdateDetails @studentId bigint AS BEGIN SET NOCOUNT ON; declare @currentStudentId bigint select @currentStudentId=StudentId from tbStudent where StudentId=@studentId update tbAdmission set AdmissionDate=getdate() where StudentId=@currentStudentId END GO
Now call the store procedure with Parameter value, if you don't provide parameter value this will produce error.
Here are few points to remember while writing a store procedure
We can call any UDF (user defined function) from stored procedures, there are difference between stored procedures and functions, learn more about UDF (user defined function)
Can I call a store procedure within a store procedure?
Yes, Store procedure can be executed from anywhere
Can I call a Trigger within a store procedure?
No, Trigger gets fired based on event on table, learn more about Trigger
Return Multiple Recrodsets using Storeprocedures 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 should also read how to read multiple recrodset using DataReader in Ado.Net