SpcCode SpcCode - 2 months ago 21
C# Question

SQL Insert Store Proc with dynamic table name

I am trying to create a store procedure for inserting new data. Basically I am getting an error about 'declare the table variable@tablename'. I tried:

create procedure [dbo].[spInsertProc](@table_name varchar(max))
as
begin
declare @name nvarchar(128);
declare @description nchar(255);
declare @tablename varchar(max);
--declare @tablename as table;
set @tablename = @table_name;
Insert Into @tablename ([name], [description])
Values (
@name,
@description)
end


Another question is this store proc secure since I am not using a query string, right?

Answer

Prepare dynamic insert statement as below:

CREATE procedure [dbo].[spInsertProc](@table_name varchar(max))
as
begin
  declare @name varchar(50)='Sandip';
  declare @description varchar(50)='SE'; 

  Declare @Query VARCHAR(MAX)
  SET @Query='Insert Into '+@table_name+' ([name], [description])
    Values (
       '''+@name+''',
        '''+@description+''')'
  EXEC(@Query);
  PRINT(@Query); 
end