Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
CREATE FUNCTION dbo.usrlst(@recid int)

DECLARE @Userlist varchar(1000)

RETURNS @userlst TABLE (@eventid int, @userlst nvarchar(1000)

AS

BEGIN

INSERT INTO @userlst

SELECT @recid, (SELECT @Userlist = COALESCE(@Userlist + '; ', '') + tbl_Users.Lname + N', ' + tbl_Users.Fname

FROM tbl_Event_Assignments INNER JOIN

tbl_Users ON tbl_Event_Assignments.Assigned_TO = tbl_Users.id

WHERE (tbl_Event_Assignments.Event_ID = @recID)) as userlst)

RETURN

END

Above is a script I have setup for a table defined function. what I am trying to accomplish is I need a field that I can relate to a table. Table A is my event list. It holds specifics about the events. table B is a list of actions and the people those actions were assigned to. They want one display to see data from the Event list and a list of the people the event was assigned to. They want the list to be in one field so the data is easly readable.

I am open to any suggestions.
Posted
Updated 24-May-11 18:03pm
v2

1 solution

I believe the logic of what you are trying to accomplish is covered on this page[^]. Aside from that:
1. You should DECLARE variables inside the BEGIN and END statements.
2. Add closing parenthesis to the RETURNS table definition.
3. Don't use @ in the column names for the RETURNS table definition.
4. Also, consider using variable, table, function, column names that are not as similar as the ones here to avoid confusion and make the function more readable.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900