SQL Server single stored procedure for insertion

Question

I am trying to create a stored procedure in SQL Server 2008 which can insert data into any table (not table specific). I created one shown below, but during execution of the procedure an exception is thrown i.e.

Invalid object name 'dbo.@table'.

Stored procedure code:

CREATE PROCEDURE dbo.sp_InsertValues
   @table varchar(15)
   , @fields varchar(100)
   , @values varchar(100)   
AS
BEGIN
   INSERT INTO [dbo].[@table] (@fields) VALUES (@values)
END
GO

Remember I checked the parameters table, columns and values parameters are not null.

To do this, you are going to have to create dynamic sql out of the parameters you are passing in. — Adam Wenger
This technique is not good because it does not use parameterized SQL for no good reason. Please do not do it this way. Research, why parameterized SQL is important before deciding on this way of inserting data. — usr

Pete Carter Pete Carter · Accepted Answer · 2012-10-21T10:05:52

You will need to build the command using dynamic SQL and then execute it. Try this:

CREATE PROCEDURE dbo.sp_InsertValues
   @table nvarchar(15)
   , @fields nvarchar(100)
   , @values nvarchar(100)   
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'INSERT INTO [dbo].' + QUOTENAME(@table) + '(' + @fields +') VALUES (' +  @values + ')'
    EXEC(@SQL)
END
GO

SQL Server single stored procedure for insertion

1 Answers