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
– Adam Wenger, Commented Oct 21, 2012 at 1:30
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
– usr, Commented Oct 21, 2012 at 10:48

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

2

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

answered Oct 21, 2012 at 10:05

Pete Carter

2,7313 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server single stored procedure for insertion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related