Pages

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

No comments:

Post a Comment

Please leave a comment for this post