Dynamic T-SQL stored procedure to insert in differents tables

Question

What is the best way to achieve this

INSERT INTO @TableName (@ColumnNames)
 EXEC sp_executesql @SQLResult;

Where @TableName, @ColumnNames, @SQLResult are varchar variables

I am trying to avoid do a separate insert for each table.

• Hogan: don't. use a case statement or investigate why your api is designed this way.
• Santiago Salaberry: @Hogan I'm trying avoid a case because are 23 differents tables should I need populate for now. The TableName is a parameter of the SP which I use to insert into the tables
• user2366842: Sounds like bad design. I'd honestly try looking into redesigning whatever process this is, if it's a viable option.
• Hogan: Just write the 23 stored procedures. If that is to hard you can make the procedures with dynamic HTML that will be more secure, run faster AND is a better design.
• Solomon Rutzky: @SantiagoSalaberry You don't want to avoid writing 23 insert procs, given the downsides. Please also see this nearly identical question on DBA.StackExchange: dba.stackexchange.com/questions/105541/…

Answer 1

The best way is to write (or generate) all reqiured procedures for all table. 23 tables times 4 procedures (insert, update, delete and select) that can be generated automatically is nothing in dev time and pain compared to the so called "generic solution".

It's a path to poor perfomance, unreadable code, sql injection hazard and countless debuging hours.

• Zohar Peled: Also, read my answer to a similar question a while back

Answer 2

First of all I appreciate all your comments. And I agree that SQL dynamic is a pain to debug (Thanks God, management studio has this possibility). And, of course there are hundreds of different solutions

I solved it in this way finally, more or less I try to explain why this solution of SQL dynamic. The client uses xlsx spreadsheets to enter certain data, so I read the spreadsheets and I insert the (data depends on the spreadsheet to insert into the proper table). Later the data in the tables are exported to XML to send a third party sofware.

SET @SEL = N'';
DECLARE sel_cursor CURSOR
FOR (SELECT sc.name as field
 FROM sys.objects so INNER JOIN sys.columns sc ON so.[object_id]=sc.[object_id]
 WHERE so.name= @TableName and sc.name not in ('InitDate', 'EndDate', 'Version', 'Status'));
SET @SEL = ''; set @i = 0;
OPEN sel_cursor
FETCH NEXT FROM sel_cursor INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
 set @sel = @sel + ', '+ @field 
 set @i = 1;
 FETCH NEXT FROM sel_cursor INTO @field
END
CLOSE sel_cursor;
DEALLOCATE sel_cursor;
SET @SQL = N''
SET @SQL = @SQL + N'SELECT * INTO XLImport FROM OPENROWSET'
SET @SQL = @SQL + N'('
SET @SQL = @SQL + N'''Microsoft.ACE.OLEDB.12.0'''+','
SET @SQL = @SQL + N'''Excel 12.0 Xml; HDR=YES;'
SET @SQL = @SQL + N'Database='+@file +''''+ ','
SET @SQL = @SQL + N'''select * from ['+ @SheetName + '$]'''+');'
EXEC sp_executesql @SQL
SET @SQL = N'';
SET @SQL = @SQL + N'
SELECT '+''''+CAST(@initDate AS VARCHAR(10))+'''' +', '+ ''''+CAST(@endDate AS VARCHAR(10))+'''' 
 + ', '+ CAST(@version AS VARCHAR(2)) +', ' +''''+@status+''''
 + @SEL 
 +' FROM DBO.XLImport '
DECLARE cols_cursor CURSOR
 FOR (Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS where table_name = @tableName);
SET @SEL = ''; set @i = 0;
OPEN cols_cursor
FETCH NEXT FROM cols_cursor INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
 set @sel = @sel + @field + ', '
 set @i = 1;
 FETCH NEXT FROM cols_cursor INTO @field
END
CLOSE cols_cursor;
DEALLOCATE cols_cursor;
SET @SEL = LEFT(@SEL, LEN(@SEL) - 1) -- remove last ,
SET @SQL = N''
SET @SQL = @SQL + N'SELECT * INTO XLImport FROM OPENROWSET'
SET @SQL = @SQL + N'('
SET @SQL = @SQL + N'''Microsoft.ACE.OLEDB.12.0'''+','
SET @SQL = @SQL + N'''Excel 12.0 Xml; HDR=YES;'
SET @SQL = @SQL + N'Database='+@file +''''+ ','
SET @SQL = @SQL + N'''select * from ['+ @SheetName + '$]'''+');'
EXEC sp_executesql @SQL
SET @SQLString =
N'INSERT INTO '+ @TableName + '('+ @SEL +') ' + @SQL;
EXEC sp_executesql @SQLString

Answer 3

Use EXECUTE sp_executesql @sql, here is example:

create proc sp_DynamicExcuteStore 
@TableName varchar(50),
@ColumnNames varchar(50),
@SQLResult varchar(max)
as
declare @sql nvarchar(max) = '
INSERT INTO '+@TableName+' ('+@ColumnNames+')
 EXEC sp_executesql '+@SQLResult
EXECUTE sp_executesql @sql
go
create proc sp_test
as
select 'test' + convert(varchar,RAND())
go
CREATE TABLE [dbo].[Test](
 [text1] [nvarchar](500) NULL
) ON [PRIMARY]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[sp_DynamicExcuteStore]
 @TableName = N'Test',
 @ColumnNames = N'text1',
 @SQLResult = N'proc_test'
SELECT 'Return Value' = @return_value
GO
SELECT TOP 1000 [text1]
 FROM [test].[dbo].[Test]