Execute Dynamic Stored procedure

  • Lidou123

    Hall of Fame

    Points: 3052

    Hello Experts!!
    Merry Xmas !!!!
    Can you please help me to write and execute a dynamic 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.


    /****** Object: Table [Config].[File_Parameters]  Script Date: 26/12/2018 06:35:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Config].[File_Parameters]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [Config].[File_Parameters](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [FileSource] [nvarchar](100) NULL,
        [Directory] [nvarchar](100) NULL,
        [DataSource] [nvarchar](100) NULL,
        [DataFileType] [nvarchar](10) NULL,
        [FieldTerminator] [nvarchar](1) NULL,
        [RowTerminator] [nvarchar](10) NULL,
        [CodePage] [nvarchar](10) NULL,
        [FirstRow] [nvarchar](10) NULL,
        [Src_Table] [nvarchar](50) NULL,
        [Sas_Table] [nvarchar](50) NULL,
    PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    SET IDENTITY_INSERT [Config].[File_Parameters] ON
    GO
    INSERT [Config].[File_Parameters] ([Id], [FileSource], [Directory], [DataSource], [DataFileType], [FieldTerminator], [RowTerminator], [CodePage], [FirstRow], [Src_Table], [Sas_Table]) VALUES (1, N'temp/mcp/Accompagnists_2.csv', N'temp/mcp/', N'KantarHotStorage', N'char', N';', N'0x0a', N'65001', N'2', N'ods.Src_Accompagnists', N'ods.Sas_Accompagnists')
    GO
    INSERT [Config].[File_Parameters] ([Id], [FileSource], [Directory], [DataSource], [DataFileType], [FieldTerminator], [RowTerminator], [CodePage], [FirstRow], [Src_Table], [Sas_Table]) VALUES (2, N'temp/mcp/Activity_2.csv', N'temp/mcp/', N'KantarHotStorage', N'char', N';', N'0x0a', N'65001', N'2', N'Ods.Src_MCP_Files', N'Ods.Sas_MCP_Files')
    GO
    SET IDENTITY_INSERT [Config].[File_Parameters] OFF
    GO

    I also created a dynamic stored procedure that I could call via SSIS.

    /
    create PROCEDURE [dbo].[usp_Alim_Sas_MCP_Files_old]
    @FileSource nvarchar (100)
    AS
    BEGIN
    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;

    GO

    But I don't know why the stored procedure doesn't execute the script.
    This is the result I have. Can you help me to execute this script ,,

  • Lidou123

    Hall of Fame

    Points: 3052

    Lidou123 - Tuesday, December 25, 2018 11:07 PM

    Hello Experts!!
    Merry Xmas !!!!
    Can you please help me to write and execute a dynamic 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.


    /****** Object: Table [Config].[File_Parameters]  Script Date: 26/12/2018 06:35:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Config].[File_Parameters]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [Config].[File_Parameters](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [FileSource] [nvarchar](100) NULL,
        [Directory] [nvarchar](100) NULL,
        [DataSource] [nvarchar](100) NULL,
        [DataFileType] [nvarchar](10) NULL,
        [FieldTerminator] [nvarchar](1) NULL,
        [RowTerminator] [nvarchar](10) NULL,
        [CodePage] [nvarchar](10) NULL,
        [FirstRow] [nvarchar](10) NULL,
        [Src_Table] [nvarchar](50) NULL,
        [Sas_Table] [nvarchar](50) NULL,
    PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    SET IDENTITY_INSERT [Config].[File_Parameters] ON
    GO
    INSERT [Config].[File_Parameters] ([Id], [FileSource], [Directory], [DataSource], [DataFileType], [FieldTerminator], [RowTerminator], [CodePage], [FirstRow], [Src_Table], [Sas_Table]) VALUES (1, N'temp/mcp/Accompagnists_2.csv', N'temp/mcp/', N'KantarHotStorage', N'char', N';', N'0x0a', N'65001', N'2', N'ods.Src_Accompagnists', N'ods.Sas_Accompagnists')
    GO
    INSERT [Config].[File_Parameters] ([Id], [FileSource], [Directory], [DataSource], [DataFileType], [FieldTerminator], [RowTerminator], [CodePage], [FirstRow], [Src_Table], [Sas_Table]) VALUES (2, N'temp/mcp/Activity_2.csv', N'temp/mcp/', N'KantarHotStorage', N'char', N';', N'0x0a', N'65001', N'2', N'Ods.Src_MCP_Files', N'Ods.Sas_MCP_Files')
    GO
    SET IDENTITY_INSERT [Config].[File_Parameters] OFF
    GO

    I also created a dynamic stored procedure that I could call via SSIS.

    /
    create PROCEDURE [dbo].[usp_Alim_Sas_MCP_Files_old]
    @FileSource nvarchar (100)
    AS
    BEGIN
    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;

    GO

    But I don't know why the stored procedure doesn't execute the script.
    This is the result I have. Can you help me to execute this script ,,

    Any help please !!!

  • Mike01

    SSChampion

    Points: 11274

    Can you step through it in the debugger?  Does the statement you created run successfully?  Lastly, try putting the Exec (@SQL) in a try catch block to see if there are any errors there

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    Your SELECT statement is just selecting the dynamic SQL, not setting it.  Try changing SELECT to SET @SQL =

    John

  • Lynn Pettis

    SSC Guru

    Points: 442332

    John Mitchell-245523 - Thursday, December 27, 2018 9:20 AM

    Your SELECT statement is just selecting the dynamic SQL, not setting it.  Try changing SELECT to SET @SQL =

    John

    Or change the + immediately following the @SQL to an =.

  • Lidou123

    Hall of Fame

    Points: 3052

    Lynn Pettis - Thursday, December 27, 2018 1:14 PM

    John Mitchell-245523 - Thursday, December 27, 2018 9:20 AM

    Your SELECT statement is just selecting the dynamic SQL, not setting it.  Try changing SELECT to SET @SQL =

    John

    Or change the + immediately following the @SQL to an =.

    I have an error 🙁

    Msg 156, Niveau 15, État 1, Procédure usp_Alim_Sas_MCP_Files_old_3, Ligne 58 [Ligne de départ du lot 6]
    Incorrect syntax near the keyword 'FROM'.

  • Lidou123

    Hall of Fame

    Points: 3052

    Lidou123 - Saturday, December 29, 2018 3:45 AM

    Lynn Pettis - Thursday, December 27, 2018 1:14 PM

    John Mitchell-245523 - Thursday, December 27, 2018 9:20 AM

    Your SELECT statement is just selecting the dynamic SQL, not setting it.  Try changing SELECT to SET @SQL =

    John

    Or change the + immediately following the @SQL to an =.

    I have an error 🙁

    Msg 156, Niveau 15, État 1, Procédure usp_Alim_Sas_MCP_Files_old_3, Ligne 58 [Ligne de départ du lot 6]
    Incorrect syntax near the keyword 'FROM'.


    /****** Object: StoredProcedure [dbo].[usp_Alim_Sas_MCP_Files_old]  Script Date: 29/12/2018 11:02:35 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    create PROCEDURE [dbo].[usp_Alim_Sas_MCP_Files_old_3]

    @FileSource nvarchar (100)

    AS

    BEGIN

    DECLARE @SQL NVARCHAR(4000)

    SET @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;

    GO

  • Lidou123

    Hall of Fame

    Points: 3052

    Hello
    I finally found the result:
    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

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply