SQLandSai: Stored Procedures in SQL Server

STORED PROCEDURES

* To implement business logic which should be executed in the database server we can use SPs.

* It is a program, present in compiled format.

* Max size of SP is 250 MB

* It can take value(s) from front end part with input parameter.

* Once the SP is executed we can take values back to front end part with output parameters.

* A SP can receive max 1024 parameters.

* The aim of SP is to move business logic closer to data.

Advantages

* To move business logic closer to data.

* To implement high performance i.e no need the logic to compile again and again

* To reduce n/w traffic i.e no need to send business logic to server again and again.

* Easy enhancements.

* Re-usability.

* Reduces task of administration.

* Providing security.

Types of SPs

* System Defined

Ex: sp_help, sp_helptext, sp_rename

* User Defined

* Created by the developer.

* T-SQL SPs.

* CLR SPs.

Steps to create SP

1. Creating SP

create proc/procedure <SP_Name>

(

parameters

)

begin

----

end

2. Executing SP

Exec <SP_Name> [parameter values]

WAP without parameters

CREATE PROCEDURE p1
AS
BEGIN
 PRINT 'SQL and Sai Technologies'
END

Execution:

EXEC p1

Ex: Create procedure which returns sum of 2 integers

Step1: Creating SP

CREATE PROCEDURE sum_sp

         AS
        BEGIN
 DECLARE @a INT
  ,@b INT
  ,@sum INT

 SET @a = 10
 SET @b = 20
 SET @sum = @a + @b

 PRINT @sum
        END

Step 2: Calling SP

EXEC sum_sp

FAQ:- Diff between S.P and View?

View Stored Procedure

1) It consists of only SELECT cmd. 1) It can have any commands.

2) It cannot take parameters. 2) It can take parameters.

3) We can call the view with select 3) Execute statement

statement.

4) It is a stored query. 4) It is a reusable code component.

Note:

1) To view the s.p definition

sp_helptext <spname>

Ex:

sp_helptext dept_Noe_sp

2) S.P definition is stored in syscomments table hence we can view it as follows.

select text from syscomments where id=object_id('dept_noe_sp')

Stored Procedure Lifecycle

1) At the time of creation its details are stored in

Sysobjects

Syscomments

Sysdepends

Note:

To find the objects used in a stored procedure

select name as objectName,xtype as object_Type from sysobjects where id=(select depid from sysdepends where id=(select id from sysobjects where name='dept_noe_sp'))

2) First time calling

* Compiled

* Execution plan is generated.

* Compiled code with execution plan is stored in procedure cache.

* Executed.

3) Next time calling

* Executed.

Note:

To view the details of cached batches

DBCC PROCCACHE

Procedure Cache

The procedure cache is used to cache the compiled and executable plans to speed up the execution of the batches.

The entries in a procedure cache are at a batch level. The procedure cache includes the following entries:

* Compiled plans

* Execution plans

* Algebraic tree

* Extended procedures

Working with Parameters

* Input Parameters

* These are used to pass data to the stored procedure at the time of calling.

* These are default type of parameters.

* We can initialize value for input parameter.

syn:

@pname type [INPUT[=value]]

Ex:

1) @eno int

2) @premium Money Input

3) @salary Money=1000

Note: If we initialize input parameter it becomes optional parameter. i.e at the time of calling s.p no need to mention value for this parameter.

Ex:- Create a stored procedure which takes 2 integers and displays sum.

CREATE PROCEDURE Sum_sp (
 @a INT
 ,@b INT
 )
AS
BEGIN
 DECLARE @c INT --declaring local variable

 SET @c = @a + @b

 PRINT @c
END --end of SP

--calling s.p

EXEC Sum_sp 10,20

EXEC Sum_sp @a = 50 ,@b = 40 --it allows to pass values in any order as well as provides  better clarity.

Using Optional Parameters

* If we initialize value to input parameter then it becomes optional parameter.

Ex:

Step 1

CREATE PROCEDURE EMP1_SP (@dno INT = NULL)
AS
BEGIN
 IF @dno IS NULL
  SELECT *
  FROM EMP
 ELSE
  SELECT *
  FROM emp
  WHERE deptno = @dno
END

Step2

EXEC EMP1_SP -- calling s.p without passing value for @dno which takes null

EXEC EMP1_sp @dno = 10 -- calling s.p for a particular dept details.

Working with OUTPUT parameters

These parameters are used to return values from the subprogram i.e return values from subprogram to client application. Here we can explicitly specify OUT keyword.

Ex: A procedure with OUTPUT parameter

CREATE PROCEDURE proc4 (
 @x INT
 ,@y INT @z INT OUTPUT
 )
AS
BEGIN
 SET @x = @x + @y
END

Execution:

DECLARE @a INT

EXECUTE proc2 500
 ,250
 ,@a OUTPUT

PRINT @a

Ex 2:

Develop a SP which takes empno and returns the provident fund and Professional tax at 12% and 50% respectively on the salary?

CREATE procedue deductions (
 @empno INT
 ,@pf MONEY OUTPUT
 ,@pt MONEY OUTPUT
 )
AS
BEGIN
 DECLARE @sal MONEY

 SELECT @sal = sal
 FROM emp
 WHERE empno = @empno

 SET @pf = @sal * 0.12
 SET @pt = @sal * 0.05
END

Execution:

DECLARE @vdf MONEY
 ,@vpt MONEY

EXEC deductions 1005
 ,@vpf OUTPUT
 ,@vpt OUTPUT

PRINT @vpf
PRINT @vpt

Develop a stored procedure to transfer amount from one account to the other within the bank table?

CREATE PROCEDURE funds_transfer (
 @srid INT
 ,@destid INT
 ,@amt MONEY
 )
AS
BEGIN
 UPDATE bank
 SET bal = bal - @amt
 WHERE custid = @srid

 UPDATE bank
 SET bal = bal + @amt
 WHERE custid = @destid
END

Execution:

EXECUTE funds_transfer 101,102,500

Note:

In the above case if the srid or destid are not present in the table then it will deduct the amount from the other or add the amount to other. To avoid this we need to use transaction management

To manage the transaction we need to identify which statement is executed and whic one failed for this we use the function @@rowcount

@@rowcount returns nof rows effected by the last statement

Handling Errors in SP (or) Working with TRY Catch block:

It is used to manage or handle run time errors. In SQL Server 200 and 2005 @@error is used to manage run time errors

TRY:

It contains the instruction that might cause an exception.

CATCH:

It execute only when run-time errors occurs.

FUNCTION DESCRIPTION

ERROR_NUMBER() Returns the number of the error.

ERROR_SERVITY() Returns the severity level.

ERROR_STATE() Returns the error state number.

ERROR_PROCEDURE() Returns the name of the SP or Trigger where the error occurred.

ERROR_LINE() Returns the line number inside the routine that caused the error

ERROR_MESSAGE() Returns the complete text of error message. The text include the values supplied for any substitutable parameters such as lengths,object names or times.

Note:

Servity levels are associated with the error. Range is b/n 0 and 25 , if it is >20 then client connection is terminated.

CREATE PROCEDURE div (
 @x INT
 ,@y INT
 )
AS
BEGIN
 DECLARE @z INT

 SET @z = 0
 SET @z = @x / @y

 PRINT ' The Output is:' + Cast(@z AS VARCHAR)
END

OutPut:

EXEC div 100 ,20

         EXEC div 100,0

To resolve it:

CREATE PROCEDURE DIV (

 @X INT
 ,@Y INT
 )
AS
BEGIN
 BEGIN TRY
  DECLARE @Z INT

  SET @Z = 0
  SET @Z = @X / @Y

  PRINT 'TEH OUTPUT IS:' + CAST(@Z AS VARCHAR)
 END TRY

 BEGIN CATCH
  PRINT ERROR_MESSAGE()
 END CATCH
END

Note:

We can write our own user defined error messages

CREATE PROCEDURE DIV (
 @X INT
 ,@Y INT
 )
AS
BEGIN
 BEGIN TRY
  DECLARE @Z INT

  SET @Z = 0
  SET @Z = @X / @Y

  PRINT 'TEH OUTPUT IS:' + CAST(@Z AS VARCHAR)
 END TRY

 BEGIN CATCH
  PRINT ERROR_MESSAGE()
 END CATCH
END

OutPut:

EXEC DIVIX 100 ,1

Note:

Raiserror statement raises the error but still net statement gets executed. Now if you want to stop the execution on the same line the code has to be enclosed within try catch blocks

CREATE PROCEDURE DIVIX (
 @X INT
 ,@Y INT
 )
AS
BEGIN
 BEGIN TRY
  DECLARE @Z INT

  SET @Z = 0

  IF @Y = 1
   RAISERROR (
     'CANNOT DIVIDE BY 1'
     ,15
     ,1
     )

  SET @Z = @X / @Y

  PRINT 'THE O/P IS:' + CAST(@Z AS VARCHAR)
 END TRY

 BEGIN CATCH
  PRINT ERROR_MESAGE()
 END CATCH
END

OutPut:

EXEC DIVIX 1001 ,1

Restrictions:

Only member of sysadmin,db_owner or db_ddladmin can create & execute SP

SQLandSai

Labels

Monday, 30 May 2016

Stored Procedures in SQL Server

2 comments: