Union in function error

[krotha]

I get a error in the function(Error8122: only the first query in a UNION statement can have a SELECT with an assignment.)

Can some one tell how to fix this.
Thanks in advance

CREATE FUNCTION Get_Path_func
(@asses_id numeric)
RETURNS varchar(255) AS
BEGIN
declare @path varchar(4000)
select @path= c.site_name_short
from deficiency_assessment a,
building b
where a.room_id = b.room_id
and a.project_id = b.project_id
and a.asses_id=@asses_id
union
select @path = c.site_name_short
from deficiency_assessment a,
campus c
where a.floor_id = c.floor_id
and a.project_id = c.project_id
return @path
END

 

[balves]

SQL Server doesn't understand how to stuff 2 fields into one variable called @path. It doesn't make sense to the parser.

Not sure what you want to do, but...

You could do the first SELECT. If @path was not NULL, then Return @path, otherwise do a second SELECT.