/***
select dbo.udfaddition(190,1) as addition
select dbo.udfaddition(default,1) as addition
**********/
create function dbo.udfaddition(@a int =10,@b int)
returns int
as
begin
return @a+@b
end
---Inline table valued function
/*
select * from udfemployeeDetails()
*/
create function udfemployeeDetails()
returns table
as
return
(select EmployeeID,ISNULL(FirstName,'')+''+ISNULL(LastName,'') as EmployeeName,D.DepartmentID,sal,D.Name as DepartmentName
from
Employee E
inner join Department D on E.DepartmentID=D.DepartmentID)
go
alter procedure uspGetEmployee(@DepartmentID INT)
as
begin
select distinct e.EmployeeID,e.FirstName+' '+e.LastName as EmployeeName,e.sal,fn.DepartmentID from Employee E
inner join udfemployeeDetails() fn on e.DepartmentID=fn.DepartmentID
where e.DepartmentID=@DepartmentID
end
exec uspGetEmployee 1
No comments:
Post a Comment