I would like to create a function that returns a concatinated string of a given field of given query. Here is what I did. And this one gives me an error. Must declare the table variable "@qry".
CREATE FUNCTION dbo.testing
(
@qry varchar(1000),
@fld varchar(100),
@separator varchar(15) = '; '
)
RETURNS varchar
AS
BEGIN
DECLARE @rslt varchar(1000)
SET @rslt =''
SELECT @rslt = @rslt + @separator + CAST(@fld as varchar(160)) FROM @qry
RETURN @rslt
END
What I am trying to do is pass a query to this function and receive a concatinated string for certain field of the query.
Is this possible?
What am I doing wrong?
EDIT: BTW I have MSSQL Server 2005;
You can't do a FROM @variable. You'd have to use dynamic SQL and make your @qry a derived table or something of that nature. However, I think that's really not the best approach. I believe this is what you're trying to do:
Make sure to read the comments on the XML alternative solution as well.