Online Training for Asp.net MVC Web Designing Development, MS SQL, Digital Marketing

SQL Stored procedures

What is Stored procedure ?
Store procedure 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.

How to write Store procedures in SQL Server

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
create storedprocedure in sql database

Store Procedure Example

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.
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 store procedure must write try catch block to implement error handling
  • Never write multiple functionalities into one store procedure, rather break the functionalities into multiple store procedure, maintain Single Responsibility Principle
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

 
SQL Stored procedure Example

Learn Stored procedure in SQL

Group Training