How to Use Output-type Variable

--Create Procedure-- 

if object_id('outputtest') is not null 

drop proc outputtest 

Go 

 

create proc outputtest 

--This is proc declare, NOT variable declare-- 

@Output_student_lastfirst nvarchar(500) output, 

@Output_Student_number int output 

As 

--This is variable declare-- 

declare @FunctionVariable varchar(50) 

set @FunctionVariable='9' 

select Top 1 @Output_student_number=student_number,@Output_Student_LastFirst=lastfirst  

from students  

where grade_level=@FunctionVariable 

order by newid()  

--Select a student on random in grade 9-- 

select @Output_student_number as IN_PROC_STDNUM,@Output_Student_LastFirst AS IN_PROC_LASTFIRST 

--Create Procedure-- 

 
 

 
 

--Output Variables--  

declare @output_new_student_lastfirst nvarchar(500)  

declare @output_new_Student_number int  

exec outputtest   

--This is how to contact Procedure only when you have more than 2 variable to output--  

@Output_student_lastfirst=@output_new_student_lastfirst output,  

@Output_Student_number=@output_new_Student_number output  

select   

@output_new_Student_number as OUTPUT_STUDNUM,  

@output_new_student_lastfirst as OUTPUT_LASTFIRST  

--Use these 2 variables as criteria--  

--Get full info about this student--  

select * from students s  

where s.lastfirst=@output_new_student_lastfirst and s.student_number=@output_new_Student_number 

 
----------------------------------------------------------------------------------------------------------------------------------------- 

----------------------------------------------------------------------------------------------------------------------------------------- 

 

--When you have only 1 Output variable in Procedure-- 

--Create Procedure--  

if object_id('outputtest') is not null  

drop proc outputtest  

go  

create proc outputtest  

--this is proc declare, NOT variable declare--  

@Output_Student_number int output  

As  

--this is variable declare--  

declare @FunctionVariable varchar(50)  

set @FunctionVariable='9'  

select Top 1 @Output_student_number=student_number 

from students   

where grade_level=@FunctionVariable  

order by newid()   

--select a student on random in grade 9--  

select @Output_student_number as IN_PROC_STDNUM 

--Create Procedure--  

 

 

 

 

 

--Output Variables--   

declare @output_new_Student_number int   

exec outputtest    

--This is how to contact Procedure only when you have only 1 variable to output--   

@output_new_Student_number output   

select    

@output_new_Student_number as OUTPUT_STUDNUM  

--Use these 2 variables as criteria--   

--Get full info about this student--   

select * from students s  

where s.student_number=@output_new_Student_number