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
)
as
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
SET @c = @a + @b
PRINT @c
END
--calling s.p
EXEC Sum_sp 10,20
or
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
