Execute Dynamic Stored procedure

• Lidou123

  Hall of Fame

  Points: 3186

  More actions

  December 25, 2018 at 11:07 pm

  #409398

  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: 3186

  More actions

  December 26, 2018 at 11:42 pm

  #2017383

  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: 11393

  More actions

  December 27, 2018 at 9:07 am

  #2017418

  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: 148809

  More actions

  December 27, 2018 at 9:20 am

  #2017425

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

  John

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 27, 2018 at 1:14 pm

  #2017451

  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: 3186

  More actions

  December 29, 2018 at 3:45 am

  #2017582

  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: 3186

  More actions

  December 29, 2018 at 3:48 am

  #2017583

  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: 3186

  More actions

  January 2, 2019 at 3:01 am

  #2017718

  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