user defined function problem

[misogsk]

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 GetCustomSkillC ategoryProficie ncyHeadcount(@L oc int)
returns table
as
return(
select 3 [member_type_id], csk.customskill categoryid [member_id], cskk.[name] [member_name], ps.proficiencyi d [proficiencyid], count(distinct ps.personid) [headcount]
from smdb_personskil lfilled 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.skillcategory id and csk.member_type _id = 2))
join SMDB_CUSTOM_SKI LLCATEGORY cskk on cskk.[id] = csk.customskill categoryid
where ps.personid in (select p.[id] from Person p where p.Location=@Loc )
group by csk.customskill categoryid , ps.proficiencyi d , cskk.[name])

Thank you

[iburyak]

You have to declare table variable and do all manipulation inside then return that variable instead of a statement
See example from help for references.

[PHP]CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1.
INSERT @reports
SELECT e.empid, e.empname, e.mgrid, e.title, 0
FROM employees e, @reports r
WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END

-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO

-- Example invocation
SELECT *
FROM fn_FindReports( '11234')
GO[/PHP]