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;
2条答案
按热度按时间imzjd6km1#
如果你想传递任何形式的动态SQL,你需要通过EXEC或(首选)sp_ExecuteSQL来执行它。如果你也在使用动态SQL,确保你的代码不会受到任何注入攻击,以免你遭受little Bobby Tables:-)的愤怒
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.