Saturday, July 3, 2010

Stored Procedure

Definition: One or more statements that has been given a name and stored within database.
  • 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)
SET @c = @a + @b
EXEC GenericErrorProc
-- Exec usage of the proc
DECLARE @c int
EXEC Proc1 1,2,@c out
-- Create a proc which will return a table
EXEC GenericErrorProc
-- Exec usage of SP
create table #temp (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.

1 comment:

  1. Stored procedures can also shield users from needing to know the details of the tables in the database. If a set of stored procedures supports all of the business functions users need to perform, users never need to access the tables directly; they can just execute the stored procedures that model the business processes with which they are familiar.