Passing temporary variable to function in sql server

First we need to define the User defined table type that will represent the definition of a table.

CREATE TYPE employeeDetails AS TABLE

(

EmpId int,

EmpName varchar(100),

Salary int,

DesignationId int,

DesignationName varchar(100),

LocationId int ,

LocationName varchar(100)

)

Creating tables

1. EmployeeMaster

2. EmployeeLocationDetails

3. EmployeeDesignationDetails

create table employeeMaster

(

EmpId int primary key,

EmpName varchar(100),

DesignationId int,

LocationId int,

Salary int

)

Create table employeeDesignationDetails

(

DesignationId int primary key identity(1,1),

DesignationName varchar(100)

)

Create table employeeLocationDetails

(

LocationId int primary key identity(1,1),

LocationName varchar(100)

)

 inserting into Tables

insert into employeeLocationDetails(LocationName)

values ('Delhi'),('Banglore')

insert into employeeDesignationDetails(DesignationName)

values ('Developer'),('Designer'),('Manager')

insert into employeeMaster(EmpName,DesignationId,LocationId,Salary)

values ('Ajay',2,1,45000),('Akhshay',2,1,35000),('Sanjay',1,2,34000),('Tarun',1,2,56000)

,('Anita',1,1,33000),('Aanchal',1,1,28000),('Aaina',2,2,27000),('Preeti',2,2,45000)

,('Rajiv',3,1,46000),('Neeraj',3,1,78000)

Creating procedure for passing variable to function

create proc A_employeeNamefromFunc

as

begin

Declare   @tmp as employeeDetails  --declaring temporary varible of table type

select e1.EmpId,e1.EmpName,e1.Salary,e2.DesignationId,e2.DesignationName,e3.LocationId,e3.LocationName into #temp from employeeMaster e1 --insertin into temporary table

left outer join employeeDesignationDetails e2 on e1.designationid=e2.designationid

left outer join employeeLocationDetails e3 on e3.locationid=e1.locationid

insert into @tmp select * from #temp --inserting into temporary varible

drop table #temp --droping temporary table

select dbo.func_EmployeeName(1,1,@tmp) as 'EmpName' --executing function

end

Creating function

create function func_EmployeeName(@DesignationId int,@LocationId int,@temp employeeDetails ReadOnly)--tempory table variable needs to be defined as read only.

returns varchar(100)

as

begin

declare @Name varchar(100)

select @Name=  EmpName from @temp where locationid=@LocationId and designationid=@DesignationId and salary>30000

return @Name

end

Executing Proc

executing the above proc will generate the following output