Hi. How can i put IF...ELSE block to the user defined function? I put it almost everyware, but there's still some errors. Can someone help me,please?
I need to set a new value for @Loc in dependency on it's input value.
Code:
alter function GetCustomSkillCategoryProficiencyHeadcount(@Loc int)
returns table
as
return(
select 3 [member_type_id], csk.customskillcategoryid [member_id], cskk.[name] [member_name], ps.proficiencyid [proficiencyid], count(distinct ps.personid) [headcount]
from smdb_personskillfilled ps
join skill s on s.[id] = ps.[skillid] --for getting skillcategoryid
join dbo.SMDB_CUSTOM_SKILLCATEGORY_MEMBERS csk on
((csk.member_id = ps.skillid and csk.member_type_id = 1)
or (csk.member_id = s.skillcategoryid and csk.member_type_id = 2))
join SMDB_CUSTOM_SKILLCATEGORY cskk on cskk.[id] = csk.customskillcategoryid
where ps.personid in (select p.[id] from Person p where p.Location=@Loc)
group by csk.customskillcategoryid , ps.proficiencyid , cskk.[name])
Thank you