Powered by Blogger.

Saturday, February 15, 2014

Stored Procedure tutorial in SQL Server 2008

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance.

Stored procedures can be used to help ensure the integrity of the database.

Compilation step is required only once when the stored procedure is created. Then after it does not required recompilation before executing unless it is modified.

Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure.

To Create:
CREATE PROCEDURE StoredProcedureName
SELECT * FROM Employee

ALTER PROCEDURE StoredProcedureName AS

EXEC "StoredProcedureName"

Execute with parameter:
EXEC StoredProcedureName @CParameterName ="value"

Below is a stored procedure that will take one input parameter and output parameter.

Create procedure GetEmployeeName
@Empid int,
@EmpName varchar(200) out
select @EmpName=FirstName+' '+LastName from EmloyeeDetail where @Empid=Empid

Below is the way to execute the stored procedure:

declare @Name varchar(50)
exec GetEmployeeName 3 ,@Name output
select @Name

Create Store procedure from management studio:

To create a stored procedure from SQL Server management studio,
Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.

Right-click Stored Procedures, and then click New Stored Procedure.