variables in procedures

[Seaspray0]

This really not a question, but rather a sample code I'm submitting. I'm new to SQL and I wasn't clear on how variables are handled in procedures (was afraid I would have to keep track of all my variables in case I used one in a procedure). I found out from my test code that the variables are local to the procedure (not really clear in the books I have). Here it is....

/* EXAMPLE
show how variables are localized in stored procedures

declare a starting @in and @out
send @p_in to and recieve @p_out from procedures
recieve as @in and send back @out in procedures
note how @in and @out in procedures has no effect
on starting @in and @out definitions when done */
use Northwind
go
create procedure dbo.process2
@in varchar(20),
@out varchar(20) output
as
print ' '
print 'running p2 @in, @out output'
if @in is null set @in='[null]'
if @out is null set @out='[null]'
print '@in='+@in+' and @out='+@out+' arms passed with p2'
set @out='p2(@out)'
print ' '
print 'set return @out='+@out
print 'returning error code=2'
print 'return(2)'
return(2)
go
create procedure dbo.process1
@in varchar(20),
@out varchar(20) output
as
print ' '
print 'running p1 @in, @out output'
if @in is null set @in='[null]'
if @out is null set @out='[null]'
print '@in='+@in+' and @out='+@out
print ' '
set @out='p1(@out)'
declare @p_in varchar(20),@p_out varchar(20),@rc int
set @p_in='p2(@in)'
set @p_out=null
set @rc=-1
print 'set @out=p1(@out)'
print 'declare; set @p_in='+@p_in
print 'declare; leave return @p_out=[null]'
print 'declare; set return code='+(convert(varchar(20),@rc))
print 'execute @rc=process2 @p_in, @p_out output'
execute @rc=process2 @in=@p_in, @out=@p_out output
print ' '
print 'done with p2, back in p1'
if @out is null set @out='[null]'
if @p_out is null set @p_out='[null]'
print '@in='+@in+' and @out='+@out+' ;parms passed with p1'
print '@p_in='+@p_in+' and @p_out='+@p_out+' ;parms passed with p2'
print '@rc return code='+(convert(varchar(20),@rc))
print ' '
print 'set @out=p1( + @p_out + )'
set @out='p1('+@p_out+')'
print 'returning error code=1'
print 'return(1)'
return(1)
go
--give it a try, note @info and @outfo defined for each proceure
declare @in varchar(20),@p_in varchar(20),@rc int
declare @out varchar(20),@p_out varchar(20)
set @in='start(@in)'
set @out='start(@out)'
set @p_in='p1(@in)'
set @p_out=null
set @rc=0

print 'declare; set @in='+@in
print 'declare; set @out='+@out
print 'declare; set @p_in='+@p_in
print 'declare; leave return @p_out=[null]'
print 'declare; set @rc return code='+(convert(varchar(20),@rc))
print 'execute @rc=process1 @p_in, @p_out output'
execute @rc=process1 @in=@p_in, @out=@p_out output
print ' '
print 'done with p1'
print '@in='+@in+' and @out='+@out+' :starting values'
print '@p_in='+@p_in+' and @p_out='+@p_out+' arms passed with p1'
print '@rc return code='+(convert(varchar(20),@rc))
print 'done'
go
drop procedure dbo.process1
drop procedure dbo.process2
go