Stored Procedures in SQL Server

Stored Procedure:

Stored Procedure is a powerful part of SQL Server. It is group of T-SQL statements compiled into single execution plan.T-SQL code inside stored procedure is stored on SQL Server in precompiled format. You can use it as many time as you need. Similar to views, stored procedure do not store data. It store the T-SQL queries with programmig logics.

Advantages:

·         It helps in modular programming. you write a stored procedure once and can use multiple times at multiple places.

·         It enhance performance due to it is pre-compiled and store and executed on server. Also it reduce network traffic because you don’t need to send a number of lines of sql query, you just need to pass Stored procedure name.

·         It provide centralized security, administration, and maintenance for common routines

o      How to create Procedure?

Basic Syntax:

CREATE PROCEDURE or CREATE PROC

          Example:

CREATE PROCEDURE dbo.proc_name

--input parameters

--output parameters

AS

BEGIN

--Variable Declaration

--Group of SQL statements

END

GO

o     How to Execute Procedure? 

Use EXEC Command of SQL server

EXEC dbo.proc_name [Parametervalue1], Parametervalue2], Parametervalue3]

o     Using Parameters in Stored Procedure:

CREATE PROCEDURE dbo.usp_Person

 (

@pid nvarchar(50) -- Input parameter

 )

AS

     BEGIN

       select *from Person where pid=@pid

     END

     GO

    Execute this SP:

    EXEC usp_Person '3'

  Output:

o     Using Stored Procedure with OUTPUT Parameters and Return values :

Stored Procedure also return Output parameter and return values. Output parameters behave similarly to input parameters, but have to be declared with the OUTPUT keyword. To get output from stored procedure you should specify output parameter with OUTPUT keyword. Example

CREATE PROCEDURE dbo.usp_Personoutput

 (

@city nvarchar(50),       -- Input parameter

@avgsalary numeric OUTPUT -- Output Parameter

 )

AS

    BEGIN

       select @avgsalary=AVG(salary) from Person where city=@city

    END

      GO

Execute Procedure:

DECLARE @avgsal numeric

EXEC usp_Personoutput 'New Delhi', @avgsalary=@avgsal OUTPUT

PRINT @avgsal

Output:

45000

o      For Returning values: Let me show you a simple example where I am trying to pass one input parameters and two output parameters.

CREATE PROC usp_Personreturn

 @city VARCHAR(50),

 @total_sal NUMERIC OUTPUT,

 @avg_sal NUMERIC OUTPUT

AS

SELECT @total_sal=SUM(salary), @avg_sal = AVG(salary) FROM Person WHERE city=@city;

IF @total_sal IS NULL AND @avg_sal IS NULL

  BEGIN

      RETURN 0

  END

  IF @avg_sal IS NULL

  BEGIN

     RETURN 1

  END

IF @total_sal IS NULL

  BEGIN

    RETURN 2

  END

ELSE

    RETURN 4

Execute Procedure:

DECLARE @total_salary NUMERIC

DECLARE @avg_salary NUMERIC

DECLARE  @return_status VARCHAR(250)

EXEC @return_status = usp_Personreturn 'New Delhi', @total_sal=@total_salary OUTPUT,@avg_sal = @avg_salary OUTPUT

PRINT @return_status

Output:

4

Using Stored Procedure With Transaction:

CREATE PROC usp_del

(

@id INT

)

AS

BEGIN

BEGIN TRANSACTION

BEGIN TRY

    DELETE FROM A WHERE id=@id

    DELETE FROM B WHERE id=@id

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION;

END CATCH

END

In case when we are executing multiple DML queries,ue transaction as if transaction fail all modifications rollbacked.

Prev--->Database Creation using T-SQL                                                         Next--->Error Handling using T-SQL