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

SQL Stored Procedure Example

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

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.

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 we write one select and one update statement, and the sp will have one mandatory parameter called studentId

CREATE PROCEDURE usp_UpdateDetails
@studentId bigint
declare @currentStudentId bigint
select @currentStudentId=StudentId from tbStudent
where StudentId=@studentId
update tbAdmission
set AdmissionDate=getdate()
where StudentId=@currentStudentId               

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
set nocount on
Select fullname, address, contactnumber from dbo.tbStudent
Select fullname, address, contactnumber from dbo.tbTeacher

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 should also read how to read multiple recrodset using DataReader in Ado.Net