SQL Server 来自多行的SQL Concat字段

e5nszbig  于 2022-12-10  发布在  其他
关注(0)|答案(2)|浏览(137)

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;

imzjd6km

imzjd6km1#

如果你想传递任何形式的动态SQL,你需要通过EXEC或(首选)sp_ExecuteSQL来执行它。如果你也在使用动态SQL,确保你的代码不会受到任何注入攻击,以免你遭受little Bobby Tables:-)的愤怒

crcmnpdw

crcmnpdw2#

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.

相关问题