How to execute dynamic SQL in a stored procedure?

Question

Can you please help me to write and execute a dynamic SQL statement in a stored procedure?

I need to import many files in my database. To do this job I use a "Bulk Insert". So I have created a parameters table to store parameters I need to create a dynamic "Bulk Insert" depending file I insert.

This table is for the users when they want to modify or add a new file to insert in the table.

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'

I expect that the stored procedure executes the Bulk Insert script inside it.

Answer 1

This is the answer. I changed the beginning of the stored procedure when I declare the variable from : SET @SQL ='' SELECT @SQL

To

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

This is the final stored procedure:

    /****** 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

Answer 2

It is not possible to reproduce your issue but there is a general trick that works very well to debug any SQL Dinamic question. Just modify your SP, after:

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

Insert the line:

PRINT @SQL;

And you will see the real command that is launching. Then, in the tab called "Messages" you can copy that code, paste in another query window and run it to understand what is the issue.

Answer 3

Just Store the Query inside a variable and use the following command.

EXEC SP_EXECUTESQL 'Variable Name'

(In your case it is @SQL) The query will be executed.