Uses of Stored Procedure in Database

It is a set of structured query language statement with given name. It is stored in the database in the compiled form. It is store in the compile form that’s why it can be shared by many program. It accept input and output parameter. It increase network traffic and increase performance. While modifying our procedure our client get updated procedure.

Syntax for Stored procedure

-- Syntax for SQL Server Stored Procedures 

                                             

CREATE  { PROC | PROCEDURE } procedure name
    [ { @parameter data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT |  
    ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } 
[;] 

 

<procedure_option> ::=  

    [ ENCRYPTION ] 

    [ RECOMPILE ] 

    [ EXECUTE AS Clause ] 

 

 

Procedure name-It is the name of stored procedure


Create-this keyword is use for creating


PROC|PROCEDURE-we can use any one of them for creating procedure


OUT|

OUTPUT-It tell that our declared parameter is an output parameter. 


   For declaring parameter we use @ sign then parameter and its


data type. When we call procedure then the value of parameter that is


declared int the procedure should be pass.

 

We can use Procedure or Proc while creating procedure.

Now we take a example to understand  with input parameterNow we create table in database name E_Salary and Inserted few records

Uses of Stored Procedure in Database

Suppose we want to make procedure for getting salary by id

Then we do following code for crating procedure

SET QUOTED_IDENTIFIER
ON
GO
 
Create proc [dbo].[Getsalary]
@EId int
AS
begin
select salary from Test.dbo.E_Salary where Id = @EId
end

 

Now for testing and executive we use EXEC and procedure name and here we pass the id of getting salary for particular id

EXEC dbo.Getsalary @EID = 2

 

Output


Uses of Stored Procedure in Database

Input and output and basically done same thing we use out or output


keyword for indicating that it return value.

                  

Now we will modify in the same procedure to declare out parameter



Ex

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Getsalary]
@EId int,
@Ename varchar(50) output
AS
begin
select salary from Test.dbo.E_Salary where Id = @EId
end

 

 

we have declare Enema as Output parameter now we will execute this procedure to test

EXEC dbo.Getsalary @EID = 1,@Ename=amit


Uses of Stored Procedure in Database

Advantage of Stored Procedure

1-Stored Procedure provide security for our data and program

2-When we use stored procedure our execution of program become fast means it allow faster execution.

3-We can modify easily if we use stored procedure.


Last updated:3/17/2018 2:09:23 AM

0 Comments

Leave Comment