来自多行的SQL Concat字段

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".

我想创建一个函数，返回给定查询的给定字段的连续字符串。这就是我做的。这个给了我一个错误。必须声明表变量“@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;

编辑：BTW我有MSSQL Server 2005;

2 个解决方案

#1

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:

你不能做一个FROM @variable。您必须使用动态SQL并使@qry成为派生表或其他类似的东西。但是，我认为这不是最好的方法。我相信这就是你要做的事情：

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.

请务必阅读有关XML替代解决方案的注释。

#2

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

如果要通过任何形式的动态SQL，则需要通过EXEC或（首选）sp_ExecuteSQL执行它。如果您使用的是动态SQL，请确保您的代码不受任何注入攻击，以免您遭受小Bobby表的愤怒:-)

#1