SQL Server:插入具有动态表名的存储过程 [英] SQL Server : insert stored procedure with dynamic table name

问题描述

我正在尝试创建一个用于存储新数据的存储过程.基本上，我收到有关声明表变量@表名"的错误.

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

我尝试过:

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?

推荐答案

准备动态插入语句，如下所示:

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

这篇关于SQL Server:插入具有动态表名的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！