Thursday, January 5, 2017

SQL Server - Functions & calling function in stored procedure

----scalar function
/***

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:

Search This Blog

DAX - Grouping on multiple columns and the count

Please go thorugh the below URL for entire soultion. http://community.powerbi.com/t5/Desktop/DAX-Grouping-on-multiple-columns-and-the-cou...