Stored Procs 101

Stored procedures are programmability features of a database engine.

Advantages of stored procs (sp)

1. Reduce server/client network traffic
2. Stronger security 
3. Modularization & Code re-usability
4. Easier maintainance
5. Improved performance 

-- 1.  basic sp

create procedure TestProc1
as 
begin --optional
select top 5 * from sales.customers 
end -- optional 

-- 2. execute a sp

exec TestProc1 -- or execute TestProc1

-- 3. alter a sp 

alter procedure TestProc1
as
begin 
select top 10 * from sales.customers
end

-- 4. drop a sp

drop procedure TestProc1

-- 5. Rename/ "sp_" are system defined stored procs

exec sp_rename 'TestProc1', 'TestProc2'

-- 6. View the definition of sp

-- approach 1

exec sp_helptext 'TestProc1'

-- approach 2

select object_definition (object_id('TestProc'))

-- approach 3

select definition 
from sys.sql_modules
where object_id = (object_id('TestProc'))

-- 7 sp with one parameter

create procedure TestProc
( -- optional 
@custid int 
) -- optional
as
select * from sales.customers where custid = @custid

-- execute sp with parameters 

exec testproc 3

--or 

exec testProc @custid = 3

-- 8  sp with more than one parameter
create procedure TestProc
@custid int,
@contacttitle varchar(30) 
as
select * from sales.customers where custid = @custid

and contacttitle = @contacttitle

-- 9 proc which returns data, use output keyword

create procedure TestProc
@custid int,
@contacttitle varchar(30),
@companyname varchar(40) output
as
select * from sales.customers where custid = @custid

and contacttitle = @contacttitle

declare @c varchar(30)

exec testproc 2, 'Owner', @companyname = @c output

select @c -- print @c

If you don't use output keyword then if the sp will return a table if called from .net code. 

If we use the output keyword then a single value is returned which can be captured as a .net data type

Error codes of sp with output keyword

0 : Successful execution
1 : Required param is not specified
2 : Specified param value is not valid
3 : Error has occurred in getting the value
4 : Null value found for variable 


-- 10 recompiling sp
There are three ways to recompile a sp

exec testproc with recompile

exec sp_recompile 'testproc'