sql server - Dynamic SQL (T-SQL) insert into (dynamic table name) using stored procedure (sp_executesql @sql)

Question

I'm struggling with a stored procedure inserting values into a table.

The table name is variable, for example, Log1

This table contains the following columns: Actual, Regulated, Supply, Expected, ControlValue all type float.

The stored procedure has the same input parameters and in addition to this the table name offcource.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO    

ALTER PROCEDURE [dbo].[InsertIntoTable](
   @TableName nvarchar(50),
   @Actual real,
   @Regulated real,
   @Supply real,
   @Expected real,
   @Control_Value real)
AS
BEGIN 
  SET NOCOUNT ON; 
  DECLARE @columnList varchar(75)
  DECLARE @SQL NVARCHAR(MAX) -- dynamic SQL should always be Unicode

  SET @SQL = N'INSERT INTO dbo.' + @TableName +  '([Actual],[Regulated],[Supply],[Expected],[Control_Value])
        VALUES('+@Actual+','+@Regulated+', '+@Supply+','+@Expected+','+@Control_Value+')'  

  EXEC sp_executesql @sql    
END  

Answer 1

First, the fact that you need to resort to dynamic SQL indicates a serious database design problem: You have multiple tables that stores the same entity, holding some identifiers in the table name.
Taking your example, assuming the tables you have are named log1, log2...logn, you should replace them with a single table called log, and have that number as another column in this table.

Having said that, I realize that in many cases, changing the database structure is not an option - and for that reason I'll show you a workaround for your problem (anything other than changing the database structure is by definition a workaround and not a solution).

The first thing you want to do is to use sysname as the data type for the table name instead of your current nvarchar(50).
sysname is the data type used internally by SQL Server for all identifiers.

The second thing you is to make sure the table actually exist, and that it has the columns you expect. This can easily be done using the built in information_schema.columns system view.

Next, you want to treat your parameters as parameters - not to concatenate them into the dynamic SQL string, but pass them as parameters to sp_executeSql.

And finally, you want to safeguard against funky chars in the table name, so you use QUOTENAME when concatenate it to your SQL:

ALTER PROCEDURE [dbo].[InsertIntoTable](
   @TableName sysname,
   @Actual real,
   @Regulated real,
   @Supply real,
   @Expected real,
   @Control_Value real)
AS
BEGIN 
  SET NOCOUNT ON; 

  DECLARE @ColumnCount int;
  SELECT @ColumnCount = COUNT(*)
  FROM Information_schema.Columns
  WHERE Table_Name = @TableName
  AND COLUMN_NAME IN([Actual],[Regulated],[Supply],[Expected],[Control_Value])

  IF @ColumnCount < 5 RETURN;


  DECLARE @SQL NVARCHAR(MAX) -- dynamic SQL should always be Unicode

  SET @SQL = N'INSERT INTO dbo.' + QUOTENAME(@TableName) +  '([Actual],[Regulated],[Supply],[Expected],[Control_Value])
        VALUES(@Actual, @Regulated, @Supply, @Expected, @Control_Value)'  

  EXEC sp_executesql 
      @sql, 
      N'@Actual real, @Regulated real, @Supply real, @Expected real, @Control_Value real',
      @Actual, @Regulated, @Supply, @Expected, @Control_Value
END 

For a more detailed explanation, check out my blog post entitled The do’s and don’ts of dynamic SQL for SQL Server.