How to create a Multi-statement table valued function?
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