Stored Procedure in sql server | Angular | ASP.NET Tutorials

For Consultation : +91 9887575540

Stay Connected :

Stored Procedure:

  • Set of logical group of SQL statements which are grouped to perform a specific task.


Benefits of Using the Stored Procedure

  • Reduces the amount of information sent to the database server.
  • Compilation step is required only once when the stored procedure is created.
  • It helps in re usability of the SQL code
  • Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure
  • it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic, then we may only need to make changes in the stored procedure and not in the files contained on the web server.

Difference between Stored Procedure and Function:

  1. A Stored Procedure may or may not return a value but a function always returns a value.
  2. We can use a function in a Stored Procedure but we can’t use a Stored Procedure in a Function

Type of Stored Procedure

  1. System Stored Procedure
  2. User Defined Stored Procedure


Types of User Defined Store Procedure:

  1. Non-parameterized Stored Procedure
  2. Parameterized Stored Procedure


Syntax for Stored Procedure

Create Procedure Procedure-name


Input parameters ,

Output Parameters (If required)




Sql statement used in the stored procedure


Stored Procedure using out Parameter

OUTPUT keyword to the parameters in the Stored Procedures can return the values of the parameters to the calling program.


create proc sp_OutEx @id int , @name varchar(50) output


select @name=Name+’,’+FatherName from Employee where id=@id


declare @fetchresult varchar(50)

exec sp_OutEx 3,@fetchresult out

select @fetchresult