sql-server - 在某些情况下，带有动态数据透视语句的 INSERT INTO 会产生未知表错误

Question

我正在使用 SQL Server，并且在存储过程中有一个动态构建的 INSERT INTO 语句。当我手动执行代码时，代码可以正常工作，但是当我执行 SP 时，它会返回一个 Unknown Table 错误，它会尝试从 select 语句创建表。

问题仅在生产服务器上。在开发服务器上，无论您执行哪种方式，它都能正常运行。因此，在我的开发服务器上，我的 Web 应用程序能够显示所需的结果。但我无法确定哪些设置或配置是相关的并且可能有所不同。开发服务器运行的 SQL 版本与生产服务器不同，但我不确定哪些版本。

我想要完成的是：我的数据库存储某种类型的表单。提交的每个表单的标题信息都存储在父表中。详细的行项目答案在子表中。（即每个编号的行项目的答案：1.01、1.02、1.03 等）每个答案都包含一个指示其是/否选择的值和一个用于注释/评论的文本字段。我的客户需要在其中一行显示提交的所有信息的表单中查看数据。因此，我正在使用枢轴将多行答案转换为同一行的列。

我这里需要使用动态 SQL 的原因是数据库中实际上存在不同的表单类型。每种类型都有自己的子行数，其中问题被分成不同的部分并相应编号。为了便于阅读，我简化了此处显示的存储过程。但是该过程接受表单类型值并使用它将结果限制为仅该类型。否则，如果结构没有变化，我就不需要动态 SQL。

这是代码：

ALTER PROCEDURE [dbo].[sp_Get_Assessment_List_Details] 

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


    -- ================================================================================================================================================
    --  Build tables that transposes answers and comments from the detail records into columns. 
    --  So multiple child records become single detail records with answer values across in columns.
    -- ================================================================================================================================================
    declare @collist nvarchar(max)
    declare @q nvarchar(max)

    -- Create a string containing all the line numbers for answers in this set.
    SET @collist = stuff((select distinct ',' + QUOTENAME(Dtl_Line_Number) 
                FROM dbo.Form_Details as D
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

    -- Once for answers
    set @q = '
    select * INTO _Temp_Form_Details from (
        select Dtl_Form_ID, Dtl_Line_Number, Dtl_Answer
        from ( select * from dbo.Form_Details ) as x
                  ) as source
    pivot (
        max(Dtl_Answer)
        for Dtl_Line_Number in (' + @collist + ')
    ) as pvt'

    exec (@q)

    -- Again for comments/notes
    SET @collist = stuff((select distinct ',' + QUOTENAME(Dtl_Line_Number)
                FROM dbo.Form_Details as D
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    set @q = '
    select * INTO _Temp_Form_Details_Notes from (
        select Dtl_Form_ID, Dtl_Line_Number, Dtl_Comment
        from ( select * from dbo.Form_Details ) as x
                  ) as source
    pivot (
        max(Dtl_Comment)
        for Dtl_Line_Number in (' + @collist + ')
    ) as pvt'

    exec (@q)


    -- ================================================================================================================================================
    --  Finally, return the Form header data joined with the transposed detail columns
    -- ================================================================================================================================================
    -- Omitted here for simplicity

    DROP TABLE _Temp_Form_Details
    DROP TABLE _Temp_Form_Details_Notes

END

再次总结一下：当作为存储过程 ( exec sp_Get_Assessment_List_Details) 执行时，它会在显示“”的第一行返回“未知表”错误exec (@q)。所以它似乎不喜欢表_Temp_Form_Details 和_Temp_Form_Details_Notes。

但是，如果我在查询窗口中手动突出显示并执行代码，它会运行良好并毫无问题地返回透视结果。此外，该错误仅发生在生产服务器上。在开发机器上，它运行良好。

我愿意接受解决此问题的方法或可以实现相同目标的其他方法。

谢谢！

标签： sql-servertsqlpivot-tablesql-insertdynamic-sql