How to create a Multi-statement table valued function?

751    Asked by JustinStewart in SQL Server , Asked on Jan 28, 2020
Answered by Justin Stewart

In Multi-statement table-valued functions: it returns the data as a table, and can use multiple statements. It can accept parameters as inputs, it can return only the one single table

alter FUNCTION dbo.udf_get_employs_increment(

 @p_lows_lab1 DEC(10,2) ,@p_lows_lab2 DEC(10,2), @p_high_slab DEC(10,2)

)

RETURNS @employ table (

EMPLOYID int,EMPLOYNAME nvarchar(50),Salary FLOAT,Salary_after_increment FLOAT

)

AS

begin

insert into @employ

select EMPLOYEE,EMPLOYEE NAME,EMPLOYEE SALARY,EMPLOYEE SALARY*1.10 from employee where EMPLOYEE SALARY< @p_lows_lab1

insert into @employ

select EMPLOYEE ID,EMPLOYEE NAME,EMPLOYEE SALARY,EMPLOYEE SALARY*1.08 from employee where EMPLOYEE SALARY between @p_lows_lab1 and @p_lows_lab2

insert into @employ

select EMPLOYEEID,EMPLOYEENAME,EMPLOYEE SALARY,EMPLOYEE SALARY*1.07 from employee where EMPLOYEE SALARY > @p_high_slab

insert into @employ

select EMPLOYEEID,EMPLOYNAME,EMPLOYSALARY,EMPLOYEE SALARY*1.07 from employ where EMPLOYSALARY between @p_lows_lab2 and @p_high_slab

return

end



Your Answer

Interviews

Parent Categories