Sql-server – INSERT INTO statement with parameters for tableName, columns and values

dynamic-sqlparametersql servert-sql

Is it possible to have an SP that looks something like this:

DECLARE @tableName = null;
DECLARE @cols = null;
DECLARE @values = null;

INSERT INTO @tableName @cols @values;

The resultant query looks like this:

INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>);

My use case is that I'm parsing Excel data into a database, i.e. a single workbook of 'type X' equates to a single row in this database. These workbooks are filled in from a template, so the data format is pretty constant. To parse the data I create a sheet in the workbook called 'DATA' or something, that looks like this:

tableName | attributeName | attributeValue | .. plus some config stuff
People    | Firstname     | Johnny         | etc
People    | Lastname      | Heinz          | etc
People    | Firstname     | Sam            | etc
People    | Lastname      | Smith          | etc

This would equate to two rows in the People table of the database (multiple rows are recognized by repeating attributeNames for a specific table).

The reasons this needs to be dynamic SQL is:

  1. I'd like this to be applicable to any table in the database
  2. I won't know how many cols a particular table has before parsing

Best Answer

  1. You can't parameterize table names or column names - you'll have to use dynamic SQL.
  2. You should also be passing the table's schema.

  3. You should be validating table names and column names. Table names are easy to check, but for column names we'll use this function:

    CREATE FUNCTION dbo.ParseColumnList
(
  @List NVARCHAR(MAX)
)
RETURNS TABLE
AS
  RETURN   
  (
    SELECT i, 
      c = LTRIM(CONVERT(SYSNAME, SUBSTRING(@List, i, 
          CHARINDEX(',', @List + ',', i) - i)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) 
          FROM sys.all_columns) AS n(i)
    WHERE i <= LEN(@List) AND SUBSTRING(',' + @List, i, 1) = ','
  );

  4. You should be using strongly-typed parameters for parameter values, to protect yourself from SQL injection vulnerabilities. Understanding where these parameter values come from will help to further refine this solution, but for now you'll just have to blindly append your values list and hope for the best:

    CREATE PROCEDURE dbo.DealersChoice
  @TableSchema   sysname,
  @TableName     sysname,
  @ColumnList    nvarchar(max),
  @ParamValues   nvarchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS ( -- make sure table exists:
    SELECT 1 FROM sys.tables AS t
      INNER JOIN sys.schemas AS s
      ON t.[schema_id] = s.[schema_id]
      WHERE t.name = QUOTENAME(@TableName)
      AND s.name = QUOTENAME(@TableSchema)
  )
  BEGIN -- raise an appropriate error here
    RETURN;
  END

  IF EXISTS ( -- make sure columns exist:
    SELECT 1 FROM dbo.ParseColumnList(@ColumnList) AS f
      LEFT OUTER JOIN sys.columns AS c ON f.c = c.name
      WHERE c.[object_id] = OBJECT_ID(QUOTENAME(@TableSchema)
        + N'.' + QUOTENAME(@TableName))
    WHERE c.name IS NULL
  )
  BEGIN -- raise appropriate error
    RETURN;
  END

  DECLARE @sql nvarchar(max) = N'INSERT '
    + QUOTENAME(@TableSchema) + N'.' + QUOTENAME(@TableName)
    + N' (' + @ColumnList + N') VALUES(' + @ParamValues + N');';

  EXEC sys.sp_executesql @sql;
END

I am not sure how you are building the @ParamValues parameter but if you have string delimiters embedded in it (like 5, 'foo', '20160206'), you'll need to double those up somewhere so that it becomes 5, ''foo'', ''20160206'' - this becomes a challenge when you have strings like O'Brien - one of just many reasons why you should be using proper parameters instead of concatenating one big string with all your values.

See the following:

Related Question