使用自定义表类型的局部变量查询报错：未声明@StringAsArray

编写了一个使用动态SQL的存储过程，未添加自定义表类型参数时运行正常，但添加@StringAsArray参数后执行报错，错误信息如下：

System.Data.SqlClient.SqlException: 'Must declare the table variable "@StringAsArray"'

存储过程脚本：

AS 

DECLARE @serverpath varchar(255)
DECLARE @query varchar(max)

BEGIN

SET @serverpath = (SELECT [path] from [param] where [platform] = 'PLMS')

SET @query='
SELECT
''PLMS'' as PLATFORM,
''PLMS''+ ''0''+ordh_sysrefno as ZINDEX,
ad_sapcode AS "SAP ADVERTISER CODE",
ad_advcde AS "PLMS ADVERTISER CODE",
ad_advnme AS "ADVERTISER NAME",
ag_sapcode AS "SAP AGENCY CODE",
ag_agencde AS "PLMS AGENCY CODE",
ag_agennme AS "AGENCY NAME",
ordh_docno AS "TO NUMBER",
ordh_createdate AS "TO CREATE DATE",
ordh_conttp AS "CONTRACT TYPE",
tt_desc AS "TELECAST TYPE",
'''' AS "PACKAGE TYPE",
'''' AS "REVENUE TYPE",
sapcode as "SAP PROGRAM CODE",
pg_prgcode as "PLMS PROGRAM CODE",
pg_prgname as "PROGRAM",
ordd_teledte AS "TELECAST DATE",
ordd_agencost AS "INTERNAL COST",
ordd_billcost AS "BILLING COST",
''PHP'' AS CURRENCY,
'''' AS PRODUCTION,
spd_cpno as "CP NUMBER",
cph_cpdte as "CP DATE",
cph_prndte as "CP PRINT DATE",
CASE ordh_conttp
WHEN ''C''
       THEN spd_invno
WHEN ''X''

       THEN spd_exinvno
WHEN ''P''
       THEN spd_pbinvno
ELSE '''' END AS "INVOICE NUMBER",
spd_stat as "STATUS"
from
' + @serverpath +'.ord_hdr INNER JOIN
' + @serverpath +'.ord_dtl ON (ordh_sysrefno = ordd_sysrefno) INNER JOIN
' + @serverpath +'.spot_dtl ON (ordd_sysrefno = spd_sysrefno and ordd_dtlno = spd_dtlno ) INNER JOIN
' + @serverpath +'.program ON (pg_prgcode = ordd_prgcode ) INNER JOIN
' + @serverpath +'.advertiser ON (ad_advcde = ordh_advcde) INNER JOIN
' + @serverpath +'.agency ON (ag_agencde = ordh_agencde)  INNER JOIN
' + @serverpath +'.cp_hdr  ON (ordh_sysrefno = cph_refno and spd_cpno = cph_cpno) INNER JOIN              
' + @serverpath +'.cp_dtl  ON (cph_cpno = cpd_cpno and cpd_dtlno = ordd_dtlno and cpd_spotno = spd_spotno) FULL OUTER JOIN

' + @serverpath +'.telecast_type ON (ordd_teletp = tt_code) left outer join
PLMSSAP.PLMSSAPSU.programs_season on (platform = ''PLMS'' and pg_prgcode = PLMScode and
cpd_teledte BETWEEN date_start AND date_end)
WHERE
cpd_cpno in (Select LTRIM(RTRIM(StringValue)) FROM @StringAsArray)
'

EXEC (@query)
END

C#调用代码：

public static DataTable SelectFromLocal(string stdproc, string name, DataTable cps)
{
    DataTable dt = new DataTable();
    dt.TableName = name;
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["BMSSAP"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(stdproc, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            var param = new SqlParameter();
            param.SqlDbType = SqlDbType.Structured;
            param.Value = cps;
            param.TypeName = "StringArray";
            param.ParameterName = "@StringAsArray";
            cmd.Parameters.Add(param);

            cmd.CommandTimeout = 60 * 60 * 60;
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                    sda.Fill(dt);
            }
        }
    }
    return dt;
}

动态SQL执行时会创建独立的执行上下文，该上下文无法直接访问外层存储过程中的表变量@StringAsArray，因此SQL Server会提示未声明该变量。

方法一：使用临时表中转数据 #

将表参数的数据插入到临时表中，临时表在整个会话周期内可见，动态SQL可以直接引用：

ALTER PROCEDURE [YourProcedureName]
    @StringAsArray StringArray READONLY -- 必须声明参数，类型为自定义表类型
AS 
DECLARE @serverpath varchar(255)
DECLARE @query varchar(max)

BEGIN
    -- 将表参数数据插入临时表
    SELECT LTRIM(RTRIM(StringValue)) AS StringValue INTO #TempCPNo FROM @StringAsArray

    SET @serverpath = (SELECT [path] from [param] where [platform] = 'PLMS')

    SET @query='
    SELECT
    ''PLMS'' as PLATFORM,
    ''PLMS''+ ''0''+ordh_sysrefno as ZINDEX,
    ad_sapcode AS "SAP ADVERTISER CODE",
    ad_advcde AS "PLMS ADVERTISER CODE",
    ad_advnme AS "ADVERTISER NAME",
    ag_sapcode AS "SAP AGENCY CODE",
    ag_agencde AS "PLMS AGENCY CODE",
    ag_agennme AS "AGENCY NAME",
    ordh_docno AS "TO NUMBER",
    ordh_createdate AS "TO CREATE DATE",
    ordh_conttp AS "CONTRACT TYPE",
    tt_desc AS "TELECAST TYPE",
    '''' AS "PACKAGE TYPE",
    '''' AS "REVENUE TYPE",
    sapcode as "SAP PROGRAM CODE",
    pg_prgcode as "PLMS PROGRAM CODE",
    pg_prgname as "PROGRAM",
    ordd_teledte AS "TELECAST DATE",
    ordd_agencost AS "INTERNAL COST",
    ordd_billcost AS "BILLING COST",
    ''PHP'' AS CURRENCY,
    '''' AS PRODUCTION,
    spd_cpno as "CP NUMBER",
    cph_cpdte as "CP DATE",
    cph_prndte as "CP PRINT DATE",
    CASE ordh_conttp
    WHEN ''C''
           THEN spd_invno
    WHEN ''X''
           THEN spd_exinvno
    WHEN ''P''
           THEN spd_pbinvno
    ELSE '''' END AS "INVOICE NUMBER",
    spd_stat as "STATUS"
    from
    ' + @serverpath +'.ord_hdr INNER JOIN
    ' + @serverpath +'.ord_dtl ON (ordh_sysrefno = ordd_sysrefno) INNER JOIN
    ' + @serverpath +'.spot_dtl ON (ordd_sysrefno = spd_sysrefno and ordd_dtlno = spd_dtlno ) INNER JOIN
    ' + @serverpath +'.program ON (pg_prgcode = ordd_prgcode ) INNER JOIN
    ' + @serverpath +'.advertiser ON (ad_advcde = ordh_advcde) INNER JOIN
    ' + @serverpath +'.agency ON (ag_agencde = ordh_agencde)  INNER JOIN
    ' + @serverpath +'.cp_hdr  ON (ordh_sysrefno = cph_refno and spd_cpno = cph_cpno) INNER JOIN              
    ' + @serverpath +'.cp_dtl  ON (cph_cpno = cpd_cpno and cpd_dtlno = ordd_dtlno and cpd_spotno = spd_spotno) FULL OUTER JOIN
    ' + @serverpath +'.telecast_type ON (ordd_teletp = tt_code) left outer join
    PLMSSAP.PLMSSAPSU.programs_season on (platform = ''PLMS'' and pg_prgcode = PLMScode and
    cpd_teledte BETWEEN date_start AND date_end)
    WHERE
    cpd_cpno in (Select StringValue FROM #TempCPNo)
    '

    EXEC (@query)
    -- 可选：手动清理临时表，会话结束后会自动删除
    DROP TABLE #TempCPNo
END

方法二：使用sp_executesql传递表参数（推荐） #

sp_executesql支持直接向动态SQL传递参数，既解决作用域问题，又能避免SQL注入风险：

ALTER PROCEDURE [YourProcedureName]
    @StringAsArray StringArray READONLY
AS 
DECLARE @serverpath varchar(255)
DECLARE @query nvarchar(max) -- 注意改为nvarchar类型，sp_executesql要求该类型

BEGIN
    SET @serverpath = (SELECT [path] from [param] where [platform] = 'PLMS')

    SET @query=N'
    SELECT
    ''PLMS'' as PLATFORM,
    ''PLMS''+ ''0''+ordh_sysrefno as ZINDEX,
    ad_sapcode AS "SAP ADVERTISER CODE",
    ad_advcde AS "PLMS ADVERTISER CODE",
    ad_advnme AS "ADVERTISER NAME",
    ag_sapcode AS "SAP AGENCY CODE",
    ag_agencde AS "PLMS AGENCY CODE",
    ag_agennme AS "AGENCY NAME",
    ordh_docno AS "TO NUMBER",
    ordh_createdate AS "TO CREATE DATE",
    ordh_conttp AS "CONTRACT TYPE",
    tt_desc AS "TELECAST TYPE",
    '''' AS "PACKAGE TYPE",
    '''' AS "REVENUE TYPE",
    sapcode as "SAP PROGRAM CODE",
    pg_prgcode as "PLMS PROGRAM CODE",
    pg_prgname as "PROGRAM",
    ordd_teledte AS "TELECAST DATE",
    ordd_agencost AS "INTERNAL COST",
    ordd_billcost AS "BILLING COST",
    ''PHP'' AS CURRENCY,
    '''' AS PRODUCTION,
    spd_cpno as "CP NUMBER",
    cph_cpdte as "CP DATE",
    cph_prndte as "CP PRINT DATE",
    CASE ordh_conttp
    WHEN ''C''
           THEN spd_invno
    WHEN ''X''
           THEN spd_exinvno
    WHEN ''P''
           THEN spd_pbinvno
    ELSE '''' END AS "INVOICE NUMBER",
    spd_stat as "STATUS"
    from
    ' + QUOTENAME(@serverpath) +'.ord_hdr INNER JOIN -- 使用QUOTENAME避免SQL注入
    ' + QUOTENAME(@serverpath) +'.ord_dtl ON (ordh_sysrefno = ordd_sysrefno) INNER JOIN
    ' + QUOTENAME(@serverpath) +'.spot_dtl ON (ordd_sysrefno = spd_sysrefno and ordd_dtlno = spd_dtlno ) INNER JOIN
    ' + QUOTENAME(@serverpath) +'.program ON (pg_prgcode = ordd_prgcode ) INNER JOIN
    ' + QUOTENAME(@serverpath) +'.advertiser ON (ad_advcde = ordh_advcde) INNER JOIN
    ' + QUOTENAME(@serverpath) +'.agency ON (ag_agencde = ordh_agencde)  INNER JOIN
    ' + QUOTENAME(@serverpath) +'.cp_hdr  ON (ordh_sysrefno = cph_refno and spd_cpno = cph_cpno) INNER JOIN              
    ' + QUOTENAME(@serverpath) +'.cp_dtl  ON (cph_cpno = cpd_cpno and cpd_dtlno = ordd_dtlno and cpd_spotno = spd_spotno) FULL OUTER JOIN
    ' + QUOTENAME(@serverpath) +'.telecast_type ON (ordd_teletp = tt_code) left outer join
    PLMSSAP.PLMSSAPSU.programs_season on (platform = ''PLMS'' and pg_prgcode = PLMScode and
    cpd_teledte BETWEEN date_start AND date_end)
    WHERE
    cpd_cpno in (Select LTRIM(RTRIM(StringValue)) FROM @StringAsArrayParam)
    '

    -- 通过sp_executesql传递表参数
    EXEC sp_executesql 
        @query,
        N'@StringAsArrayParam StringArray READONLY', -- 定义参数类型
        @StringAsArrayParam = @StringAsArray -- 传递实际参数
END

注意事项 #

1. 确保存储过程开头正确声明@StringAsArray参数，类型为自定义的StringArray表类型，且标记为READONLY（表参数必须是只读的）
2. 使用sp_executesql时，动态SQL字符串必须是nvarchar(max)类型
3. 建议使用QUOTENAME处理@serverpath，防止SQL注入攻击

内容的提问来源于stack exchange，提问作者xtian