Stored Procedure in sql server

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)

)

As

Begin

Sql statement used in the stored procedure

End

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.

Example

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

as

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

 

declare @fetchresult varchar(50)

exec sp_OutEx 3,@fetchresult out

select @fetchresult