SQL Server动态调用带表类型参数的自定义函数报错求助

问题背景 #

此前通过动态SQL调用自定义函数FUNC_STOCK_VALUATION_COST_TTYPE可正常运行，为该函数新增表类型参数@CostTable（类型为dbo.TTCostTable）后，静态调用正常，但动态SQL调用报错。

原正常动态调用代码 #

DECLARE @DynamicParameterValue AS Nvarchar(MAX)= '''2023-07-04'', ''105'', ''2023-07-04'', 10, ''AAAA'',1, 1, 1, 0';
Declare @SQL As nvarchar(max) = 'SELECT  *                                                
FROM [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE](' + REPLACE(@DynamicParameterValue,'"','') +  ')' 
EXEC(@SQL)

表类型定义 #

CREATE TYPE dbo.TTCostTable AS TABLE (
    ID int,
    Code int,
    DocumentDate date,
    ItemCode int,
    CostPrice float,
    Quantity float,
    Amount float,
    SequenceNo int,
PRIMARY KEY ( ID ),
INDEX IX_ObjectId_OperatorType ( ItemCode, DocumentDate desc )
);

更新后的函数参数列表 #

CREATE FUNCTION [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE]                                                                                                  
(@FromDate date,                                                                        
@OrganizationCode Nvarchar(MAX),                                                    
@ToDate date,                                               
@GroupCode int,                                                     
@LegalOrganizationKey Nvarchar(60),                                                    
@IsCharacteristicFilter Tinyint,                                                   
@IsOrganizationFilter Tinyint,                                               
@CostMethod Tinyint,         
@CostEvaluateMethod Tinyint,
@CostTable dbo.TTCostTable READONLY     --- < newly added table type field....
)

正常静态调用代码 #

DECLARE @CostTable AS TTCostTable;
Insert Into @CostTable Exec PROC_GET_ITEM_WEIGHTED_AVERAGE_COST 'CADC',1,1,'','2023-03-07'    
SELECT  *                                                
FROM [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE]('2023-07-04','105','2023-07-04',10,'AAAA',1,1,1,0,@CostTable)

报错的动态SQL尝试代码 #

Declare @SQL As nvarchar(max) = '
DECLARE @CostTable AS TTCostTable;
Insert Into @CostTable Exec PROC_GET_ITEM_WEIGHTED_AVERAGE_COST ''AAAA'',1,1,'',''2023-03-07''
DECLARE @DynamicParameterValue AS Nvarchar(MAX)= ''2023-07-04'', ''105'', ''2023-07-04'', 10, ''AAAA'',1, 1, 1, 0,@CostTable
SELECT  *                                                
FROM [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE] (@DynamicParameterValue) )'
EXEC(@SQL)

错误原因分析 #

1. 语法错误：报错代码中试图将多个参数（包括表变量）赋值给单个NVARCHAR变量@DynamicParameterValue，这是不允许的；且函数调用时用单个变量代替10个参数，末尾还多了一个冗余括号。
2. 表类型参数传递方式错误：表类型参数无法通过字符串拼接的方式传递，必须在动态SQL的作用域内声明、填充并直接使用。
3. 字符串转义错误：插入表变量的存储过程调用中，空字符串的转义写法错误（',''应该改为''''）。

解决方案 #

方案1：直接在动态SQL内处理表变量并调用函数 #

在动态SQL内部声明表变量、填充数据，然后逐个传递函数参数，避免打包参数的错误写法：

DECLARE @SQL AS NVARCHAR(MAX) = N'
DECLARE @CostTable AS dbo.TTCostTable;
-- 修正空字符串的转义写法
INSERT INTO @CostTable EXEC PROC_GET_ITEM_WEIGHTED_AVERAGE_COST ''AAAA'', 1, 1, '''', ''2023-03-07'';
SELECT *
FROM [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE]
(
    ''2023-07-04'',
    ''105'',
    ''2023-07-04'',
    10,
    ''AAAA'',
    1,
    1,
    1,
    0,
    @CostTable
);';
EXEC sp_executesql @SQL;

方案2：外部传递参数（避免硬编码，降低SQL注入风险） #

如果需要从外部传入参数（如日期、组织代码），使用sp_executesql配合参数列表传递，同时在动态SQL内部处理表变量：

-- 外部定义参数值
DECLARE @FromDate DATE = '2023-07-04',
        @OrganizationCode NVARCHAR(MAX) = '105',
        @ToDate DATE = '2023-07-04',
        @GroupCode INT = 10,
        @LegalOrganizationKey NVARCHAR(60) = 'AAAA',
        @IsCharacteristicFilter TINYINT = 1,
        @IsOrganizationFilter TINYINT = 1,
        @CostMethod TINYINT = 1,
        @CostEvaluateMethod TINYINT = 0,
        -- 存储过程参数
        @ProcOrgKey NVARCHAR(100) = 'AAAA',
        @ProcParam2 INT = 1,
        @ProcParam3 INT = 1,
        @ProcEmptyStr NVARCHAR(100) = '',
        @ProcDate DATE = '2023-03-07';

DECLARE @SQL AS NVARCHAR(MAX) = N'
DECLARE @CostTable AS dbo.TTCostTable;
INSERT INTO @CostTable EXEC PROC_GET_ITEM_WEIGHTED_AVERAGE_COST @P_OrgKey, @P_Param2, @P_Param3, @P_EmptyStr, @P_Date;
SELECT *
FROM [dbo].[FUNC_STOCK_VALUATION_COST_TTYPE]
(
    @P_FromDate,
    @P_OrgCode,
    @P_ToDate,
    @P_GroupCode,
    @P_LegalOrgKey,
    @P_CharFilter,
    @P_OrgFilter,
    @P_CostMethod,
    @P_CostEvalMethod,
    @CostTable
);';

-- 使用sp_executesql传递参数，避免SQL注入
EXEC sp_executesql @SQL,
    -- 参数类型定义
    N'@P_FromDate DATE, @P_OrgCode NVARCHAR(MAX), @P_ToDate DATE, @P_GroupCode INT, @P_LegalOrgKey NVARCHAR(60), 
      @P_CharFilter TINYINT, @P_OrgFilter TINYINT, @P_CostMethod TINYINT, @P_CostEvalMethod TINYINT,
      @P_OrgKey NVARCHAR(100), @P_Param2 INT, @P_Param3 INT, @P_EmptyStr NVARCHAR(100), @P_Date DATE',
    -- 参数值传递
    @FromDate, @OrganizationCode, @ToDate, @GroupCode, @LegalOrganizationKey,
    @IsCharacteristicFilter, @IsOrganizationFilter, @CostMethod, @CostEvaluateMethod,
    @ProcOrgKey, @ProcParam2, @ProcParam3, @ProcEmptyStr, @ProcDate;

关键注意事项 #

• 表类型变量的作用域仅限当前批处理，无法直接通过sp_executesql的参数列表从外部传递到动态SQL内部，必须在动态SQL内部声明并填充。
• 始终使用sp_executesql代替直接EXEC(@SQL)，避免SQL注入风险，同时提升执行计划重用率。
• 字符串转义时，单引号在动态SQL中需要用两个单引号表示，空字符串需写为''''。

内容的提问来源于stack exchange，提问作者Parth M. Dave