SQL Server : insert stored procedure with dynamic table name

Question

I am trying to create a stored 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 stored procedure secure since I am not using a query string, right?

You cannot use a variable for the tablename in any sql statement. — Gregg
– Gregg, Commented Sep 18, 2016 at 5:00

Sandip - Frontend Developer · Accepted Answer · 2016-09-18 05:00:40Z

2

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

answered Sep 18, 2016 at 5:00

Sandip - Frontend Developer

14.7k5 gold badges38 silver badges62 bronze badges

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

1 Comment

Caius Jard Over a year ago

I disapprove of your advocating an approach susceptible to SQL Injection hacking

Hamza Muhammad Khan · Accepted Answer · 2021-12-17 07:49:11Z

Here is the dyaminc Query to create stored procedure with insert statement with columns and paramaters

DECLARE @table_name varchar(255) = 'table_name'
  DECLARE @v_col varchar(MAX)
  DECLARE @v_param varchar(MAX)
  DECLARE @SP_param varchar(MAX)
  DECLARE @sp_type varchar(10) = 'Insert'

    SET @v_col = STUFF(
                 (SELECT ',' + '['+c.name+']'
                 FROM sys.tables t  JOIN sys.columns c ON t.object_id = c.object_id
                            WHERE t.name = @table_name
                            AND c.is_identity = 0
                     FOR XML PATH ('')), 1, 1, ''
                        )
    SET @v_param =  STUFF(
                     (SELECT ',' + '@'+c.name
                     FROM sys.tables t  JOIN sys.columns c ON t.object_id = c.object_id
                            WHERE t.name = @table_name
                            AND c.is_identity = 0
                     FOR XML PATH ('')), 1, 1, ''
                        )

    SET @SP_param = STUFF(
                     (SELECT ',' + '@'+c.name+' '+y.name+' '+(CASE WHEN y.name = 'varchar' THEN '('+CAST(c.max_length as varchar)+')' ELSE '' END)
                     FROM sys.tables t  JOIN sys.columns c ON t.object_id = c.object_id
                     JOIN sys.types y ON y.user_type_id = c.user_type_id
                            WHERE t.name = @table_name
                            AND c.is_identity = 0
                     FOR XML PATH ('')), 1, 1, ''
                        )
  Declare @Query VARCHAR(MAX)
  DECLARE @SPQuery VARCHAR(MAX)
  SET @SPQuery = 'CREATE PROCEDURE '+@sp_type+''+@table_name+' ('+@SP_param+')
  AS BEGIN'
  SET @Query='Insert Into '+@table_name+' ('+@v_col+')
    Values (
        '+@v_param+')
        END'


  PRINT(@SPQuery); 
  PRINT(@Query);

Collectives™ on Stack Overflow

SQL Server : insert stored procedure with dynamic table name

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related