CODEWITHSUNDEEP
sql-serversql-server-2008
syed mohsin
syed mohsin

Reputation: 2938

SQL Server single stored procedure for insertion

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.

Upvotes: 0

Views: 400

Answers (1)

Pete Carter
Pete Carter

Reputation: 2731

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

Upvotes: 2

Related Questions