SQL Concat field from multiple rows.

Question

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;

Answer 1

If you want to pass through any form of dynamic SQL, you need to execute it via EXEC or (preferred) sp_ExecuteSQL. Make sure your code's not subject to any injection attacks if you're using dynamic SQL as well lest you suffer the wrath of little Bobby Tables :-)

Answer 2

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:

http://geekswithblogs.net/mnf/archive/2007/10/02/t-sql-user-defined-function-to-concatenate-column-to-csv-string.aspx

Make sure to read the comments on the XML alternative solution as well.