Stored Procedure

Definition: One or more statements that has been given a name and stored within database.

Advantage:

• Can be used for modularized programming
• Can be used for security purpose like end user would never know how the final result/details are coming, what are the table name and all that would be hidden from end user.
• Can be in a compiled state so execution will be faster
• Reduces network traffic

Type of SPs

      · System SPs

o Starting with sp_

o E.g. (sp_helptext, sp_executesql, sp_tables, sp_rename, etc.)

o Stored in master db

o We can create UDSP with the same name but it will never be executed even if we execute like DBName.SchemaName.ProcName. SQL server internally first checks it in master db and if it finds an SP with the same name, it will execute that SP

       · User Defined SPs

o Created by User

o Nested SPs will be upto 32 levels

o Excepts input and output parameter, also excepts UDT

o You cannot have Go, Use DB, Create Proc/View/Function inside the body of SP

       · Extended SPs

o Starting with XP_

o E.g. (XP_SendMail, etc.)

o It is for all System level functions

       · Local Temporary SPs

o Starting with #

o Local scope

       · Global Temporary SPs

o Starting with ##

o Global scope until SQL server is up

-- Create a Proc with OUTPUT param

CREATE PROC Proc1 (@a int, @b int, @c int OUT)

AS

BEGIN

BEGIN TRY

SET @c = @a + @b

END TRY

BEGIN CATCH

EXEC GenericErrorProc

END CATCH

END

-- Exec usage of the proc

DECLARE @c int

EXEC Proc1 1,2,@c out

SELECT @c

-- Create a proc which will return a table

CREATE PROC Proc1

AS

BEGIN

BEGIN TRY

SELECT A,B FROM TableName

END TRY

BEGIN CATCH

EXEC GenericErrorProc

END CATCH

END

-- Exec usage of SP

create table #temp (a int, b int)

insert into #temp (a) exec proc1

Something Extra

• If you want to execute an SP at the time of SQL Server start… you have to use following option

SP_PROCOPTION (‘SPName’, ‘startup’,’true’)

• If you create an SP with “WITH RECOMPILE” option, every time it will compile and execute. Preferred only when you have a variety of different input parameters, otherwise hits performance.
• WITH ENCRYPTION will encrypt the code. Not even systadmin will be able to see. However they can alter the SP.