如何在不使用存储过程的情况下在表中返回值 exec ?

Question

我想要返回表函数，但错误是

在

函数中无效使用副作用运算符"INSERT EXEC"。

我的代码：

CREATE FUNCTION [dbo].[Fn_GetTable] 
(@ID AS NVARCHAR(MAX))
RETURNS 
@Tbl TABLE (ID NVARCHAR(MAX)) 
AS
BEGIN
DECLARE @str AS NVARCHAR(MAX)
SET @str = 'SELECT * FROM support WHERE support IN ('+@ID+')'
INSERT INTO @Tbl (ID)
EXEC sp_executesql @str
RETURN 
END

Answer 1

UDF 中禁止包含sp_executesql的过程。 https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine

我不明白为什么你在这里需要动态。尝试

Create FUNCTION [dbo].[Fn_GetTable] (
@ID as nvarchar(MAX)
)
RETURNS 
@Tbl TABLE (
ID nvarchar(MAX)
) AS
BEGIN
INSERT INTO  @Tbl (ID)
select * 
from support  
where support in (@ID)
RETURN 
END

如果参数应该表示一个列表，你最好使用 UDT。 https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-types/working-with-user-defined-types-in-sql-server .演示

CREATE TYPE dbo.UDT_IDS AS TABLE (ID  NVARCHAR(MAX)) ;

并将其用作参数

CREATE FUNCTION [dbo].[Fn_GetTable] (
@IDS as dbo.UDT_IDS READONLY
)
RETURNS 
@Tbl TABLE (
ID NVARCHAR(MAX)
) AS
BEGIN
INSERT INTO  @Tbl (ID)
SELECT support 
FROM support  
WHERE support in (SELECT ID FROM @IDS)
RETURN 
END