如何在存储过程中执行动态SQL？

Question

您能帮助我在存储过程中编写和执行动态SQL语句吗？

我需要在我的数据库中导入许多文件。为了完成这项工作，我使用了“批量插入”。因此，我创建了一个参数表来存储创建动态“批量插入”所需的参数，这取决于我插入的文件。

此表适用于想要修改或添加要在表中插入的新文件的用户。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Alim_Sas_MCP_Files]
    @FileSource NVARCHAR(100)  
AS
BEGIN
    DECLARE @SQL NVARCHAR(4000) 
    DECLARE @SQL_ NVARCHAR(4000) 

    SET @SQL =''
    SELECT @SQL + 'TRUNCATE TABLE  [Ods].[SRC_MCP_Files];
                    BULK INSERT  [Ods].[Src_MCP_Files]
                    FROM ''' + [FileSource]  + '''  
                    WITH  
                       (DATA_SOURCE = ''' + [DataSource] +''', 
                        DATAFILETYPE = ''' + [DataFileType] + ''',  
                        FIELDTERMINATOR = ''' + [FieldTerminator]  + ''',  
                        ROWTERMINATOR = ''' + [RowTerminator]+ ''',
                        CODEPAGE =  ' + CAST([CodePage] AS NVARCHAR(10))+ ',  
                        FIRSTROW = ' +  CAST([FirstRow] AS NVARCHAR(10)) +', 
                        TABLOCK 
                       );

            INSERT INTO [Ods].[Sas_MCP_Files]
                (ResponseCode, [CountryCode], [CountryLabel], Code, Label,
                 Lang, Lang_2, [DateExtraction], [IdDateExtraction])
                SELECT ResponseCode = ResponseCode+''_''+ [CountryCode] + ''_''+Code , 
            [CountryCode]
                  ,[CountryLabel]
                  ,Code
                  , Label
                 ,  Lang
                  , Lang_2
                  ,[DateExtraction]= getdate ()
                  ,[IdDateExtraction] = convert(int,convert(varchar,getdate(),112)) 
              FROM  [Ods].[SRC_MCP_Files] '

              FROM  [Config].[File_Parameters]
              WHERE FileSource = @FileSource


    EXEC (@SQL)
END;


 EXEC [dbo].[usp_Alim_Sas_MCP_Files] 'temp/mcp/Accompagnists_2.csv'

我期望存储过程执行其中的Bulk Insert脚本。

Answer 1

不可能重现您的问题，但是有一个通用的技巧可以很好地调试任何SQL Dinamic问题。只需在以下情况下修改您的SP：

SET @SQL_ = ' EXEC ''' + @SQL + ' ;'

插入以下行：

PRINT @SQL;

您将看到正在启动的真正的命令。然后，在名为"Messages“的选项卡中，您可以复制该代码，粘贴到另一个查询窗口中，然后运行它以了解问题所在。

Answer 2

只需将查询存储在一个变量中，并使用以下命令。

EXEC SP_EXECUTESQL 'Variable Name'

(在本例中为@SQL)查询将被执行。

Answer 3

这就是答案。在声明变量from时，我更改了存储过程的开头: SET @SQL ='‘SELECT @SQL

至

SET @SQL ='‘SELECT @SQL=@SQL

这是最后一个存储过程：

    /****** Object:  StoredProcedure [dbo].[usp_Alim_Sas_MCP_Files]    Script Date: 02/01/2019 10:51:56 ******/
        SET ANSI_NULLS ON
        GO

        SET QUOTED_IDENTIFIER ON
        GO







        CREATE   PROCEDURE [dbo].[usp_Alim_Sas_MCP_Files]


         @FileSource nvarchar (100) 


         AS

        BEGIN


        DECLARE @SQL NVARCHAR(4000) 


        SET @SQL =''
        SELECT @SQL=@SQL + '
        TRUNCATE TABLE  [Ods].[SRC_MCP_Files];
        BULK INSERT  [Ods].[Src_MCP_Files]
                   FROM ''' + [FileSource]  + '''  
                   WITH  
                   (    
                     DATA_SOURCE = ''' + [DataSource] +''', 
                     DATAFILETYPE = ''' + [DataFileType] + ''',  
                      FIELDTERMINATOR = ''' + [FieldTerminator]  + ''',  
                      ROWTERMINATOR = ''' + [RowTerminator]+ ''',
                     CODEPAGE =  ' + cast ([CodePage] as nvarchar (10))+ ',  
                     FIRSTROW = ' +  cast ([FirstRow] as nvarchar (10)) +', 
                     TABLOCK 
                   ) 
        ;
        INSERT INTO [Ods].[Sas_MCP_Files]
        (   Id
        , ResponseCode
        , [CountryCode]
        ,[CountryLabel]
        ,Code
        , Label
        ,  Lang
        , Lang_2
        ,[DateExtraction]
        ,[IdDateExtraction]
        )
        SELECT  
        Id = '+ cast (Id as nvarchar(10)) +'
        , ResponseCode = ResponseCode+''_''+ [CountryCode] + ''_''+Code , 
        [CountryCode]
              ,[CountryLabel]
              ,Code
              , Label
             ,  Lang
              , Lang_2
              ,[DateExtraction]= getdate ()
              ,[IdDateExtraction] = convert(int,convert(varchar,getdate(),112)) 
          FROM  [Ods].[SRC_MCP_Files] '

          FROM  [Config].[File_Parameters]
          WHERE FileSource = @FileSource


          exec (@SQL)


        END;

        GO