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

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

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

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.