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;

do it like: Set @rslt=NULL; SELECT ISNULL(@rslt+@separator,'')+... so you don't get a @separator at the start of the @rslt — KM.
– KM., Commented Jul 24, 2009 at 21:32

Callie J · Accepted Answer · 2009-07-24 21:17:16Z

3

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 :-)

answered Jul 24, 2009 at 21:17

Callie J

31.4k6 gold badges74 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Glorfindel · Accepted Answer · 2022-12-06 15:10:02Z

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.

edited Dec 6, 2022 at 15:10

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

answered Jul 24, 2009 at 21:18

ss2k

1,2882 gold badges13 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL Concat field from multiple rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related