SQL 接收错误“包含在函数中的选择语句无法将数据返回给客户端”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15960094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Receiving error "Select statements included within a function cannot return data to a client"
提问by Ankur
Receiving an error when attempting to use a Select statement in a function. The error states:
尝试在函数中使用 Select 语句时收到错误。错误指出：
Msg 444, Level 16, State 2, Procedure JDE_GetWhereClause_test, Line 26
Select statements included within a function cannot return data to a client.
消息 444，级别 16，状态 2，过程 JDE_GetWhereClause_test，第 26 行
包含在函数中的 Select 语句无法向客户端返回数据。
Any ideas?
有任何想法吗？
CREATE FUNCTION [dbo].[JDE_GetWhereClause_test]
(
@tablename as varchar
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
Declare @ResultVar as varchar(max)
-- Add the T-SQL statements to compute the return value here
set @tablename = 'F0101'
Declare @Sql nvarchar(max)
Declare my_cur cursor for
SELECT fsuser FROM dbo.JDE_ExRowSecurity where fsuser = fsuser;
Declare @fsuser as nchar(15)
open my_cur;
fetch next from my_cur;
while @@fetch_status = 0
begin
fetch next from my_cur into @fsuser;
set @ResultVar += ',' + @fsuser;
end;
close my_cur;
deallocate my_cur;
-- Return the result of the function
RETURN @ResultVar
END
回答by AjV Jsy
Try playing with something like...
尝试玩类似...
CREATE FUNCTION [dbo].[JDE_GetWhereClause_test]
(
@tablename as varchar
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
Declare @ResultVar as varchar(max)
-- Add the T-SQL statements to compute the return value here
set @ResultVar = (select STUFF((SELECT ',', fsuser as [text()]
FROM dbo.JDE_ExRowSecurity
FOR XML PATH ('')), 1, 1, '') as blah)
-- Return the result of the function
RETURN @ResultVar
END
select 'Answer is: '+[dbo].[JDE_GetWhereClause_test]('whatever')
select 'Answer is: '+[dbo].[JDE_GetWhereClause_test]('whatever')
回答by rsreji
Found this MSDN articlehelpful and it resolved my issue:
发现这篇MSDN 文章很有帮助，它解决了我的问题：
RETURN (SELECT GETDATE());
Returning values directly from a select statement, avoiding variable.
直接从 select 语句返回值，避免变量。
