What is Stored procedure and how to create in SQL Server ?

 

What is Stored Procedure and How to Create in SQL Server?

Generally definition of Stored Procedure in simple terms is ‘Stored Procedure is a pre compiled SQL Statements.’ That means we save our SQL Query as Stored Procedure so that whenever we have requirement to execute that query we will not again write the same query we will just call it by stored procedure name.

Because we already save that query as stored procedure. That is the main use or benefit of stored procedure.

Now How to create Stored Procedure:

Go to SQL Server ->Select Your Database -> Go to Programmability -> Select Stored Procedure -> Right Click on it.

Basic syntax like:

CREATE PROCEDURE <PROCEDURE NAME>

AS

BEGIN

-- SQL STATEMENTS

END

For example suppose we have a Employee table and we need to create stored procedure we specific detail like:

CREATE PROCEDURE USP_SELECTEMPLOYEEDETAILS

AS

BEGIN

SELECT * FROM EMPLOYEE

WHERE Department = 'HR'

END

 Once you create stored procedure and when you execute , it will automatically saved under stored procedure folder of your database.

How to execute stored Procedure: EXEC USP_SELECTEMPLOYEEDETAILS

 

The above stored procedure is simple stored procedure with single sql statements but here it can be depend on your requirement.

Stored Procedure with Parameter:

CREATE PROCEDURE USP_SELECTEMPLOYEEDETAILS (@department varchar(50))

AS

BEGIN

SELECT * FROM EMPLOYEE

WHERE Department = @department

END

 How to execute stored Procedure: EXEC USP_SELECTEMPLOYEEDETAILS @department = 'HR'

 How to drop stored Procedure :   DROP PROCEDURE USP_SELECTEMPLOYEEDETAILS

 What is Cursor in SQL Server

What is CTE in SQL Server ?

Interview Question and Answer of SQL ?