Complete Guide of store procedure with return and output parameters in ...

create database employee



use employee



create table tblemployee

(

id int primary key identity,

name nvarchar(20),

gender nvarchar(20),

dept_id int

)





insert into tblemployee values('salman','male',201)

insert into tblemployee values('raza','male',241)

insert into tblemployee values('ali','male',241)

insert into tblemployee values('sana','female',601)

insert into tblemployee values('sami','male',101)

insert into tblemployee values('test','female',201)



select * from tblemployee





create proc insert_tblemp

(

@name nvarchar(20),

@gender nvarchar(20),

@dept_id int,

@NewId int output

)

as



begin



insert into tblemployee values(@name,@gender,@dept_id)





 Select @NewId = SCOPE_IDENTITY()





end







declare @id int

execute insert_tblemp 'umar','male',231,@id out

print @id







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



 Create Procedure spGetTotalCountOfEmployees1

@TotalCount int output

as

Begin

 Select @TotalCount = COUNT(ID) from tblEmployee

End





Declare @TotalEmployees int

Execute spGetTotalCountOfEmployees1 @TotalEmployees Output

Select @TotalEmployees



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



Create Procedure spGetTotalCountOfEmployees2

as

Begin

 return (Select COUNT(ID) from tblemployee)

End



Declare @TotalEmployees int

Execute @TotalEmployees = spGetTotalCountOfEmployees2

Select @TotalEmployees

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





select * from tblemployee























 Create Procedure spGetNameById0

@Id int,

@Name nvarchar(20) Output

as

Begin

 Select @Name = Name from tblemployee Where Id = @Id

End





Declare @EmployeeName nvarchar(20)

Execute spGetNameById0 1, @EmployeeName out

Print 'Name of the Employee = ' + @EmployeeName







Create Procedure spGetNameById01

@Id int

as

Begin

 Return (Select Name from tblEmployee Where Id = @Id )

End







Declare @EmployeeName nvarchar(20)

Execute @EmployeeName = spGetNameById01 1

Print 'Name of the Employee = ' + @EmployeeName