在SQL Server存储过程中插入@tablename

I am trying to write the Stored Procedure that take two parameters. One is Identity column and another one about the Name (may be table name)

我正在尝试编写带有两个参数的存储过程。一个是Identity列,另一个是Name(可能是表名)

Table Structure is

表结构是

    ColumnName    DataType
    ----------------------
    ID            BIGINT
    TableName     VARCHAR(100)

Stored Procedure that I have written is

我写的存储过程是

    CREATE PROCEDURE [dbo].[SET_TABLE_VAL] 

@TableName VARCHAR(100),
@ID BIGINT OUTPUT   
    AS
    BEGIN

DECLARE @TableQuery NVARCHAR(MAX)
DECLARE @Table VARCHAR(100)

SET @Table = @TableName

SET @TableQuery = 'INSERT INTO @Table(TableName)VALUES(@TableName)'

    SELECT @TableQuery = REPLACE(@TableQuery,'@Table',@TableName)
    SELECT @TableQuery = REPLACE(@TableQuery,'@TableName',@TableName)
SET @ID = @@IDENTITY

EXEC(@TableQuery)

    END

This Procedure returns "Invalid Column Name" (at Input TableName) error. I am not able to find what is the mistake in this. Any helps would be appreciated. Thanks in Advance.

此过程返回“无效列名称”(在输入表名称处)错误。我无法找到这个中的错误。任何帮助将不胜感激。提前致谢。

2 个解决方案

#1

A much cleaner and safer approach would be something like this...

一种更清洁,更安全的方法就是这样......

CREATE PROCEDURE [dbo].[SET_TABLE_VAL]
@TableName VARCHAR(100),     --<-- I would use nvarchar(128) the maximum lenght
@ID BIGINT OUTPUT               -- a table name can be 
AS
BEGIN

DECLARE @TableQuery NVARCHAR(MAX)


SET @TableQuery = N'INSERT INTO @Table(TableName) VALUES(@TableName) '
                 + N'SET @ID = SCOPE_IDENTITY(); '


EXECUTE sp_executesql @TableQuery
                     , N'@TableName VARCHAR(100), @ID BIGINT OUTPUT'
                     , @TableName , @ID OUTPUT

END

@@IDENTITY Vs SCOPE_IDENTITY()

@@ IDENTITY与SCOPE_IDENTITY()

@@IDENTITY will return the last generated Identity number for this table in any connections, SCOPE_IDENTITY() will only return the last generated Identity in this current connection.

@@ IDENTITY将在任何连接中返回此表的最后生成的标识号,SCOPE_IDENTITY()将仅返回当前连接中最后生成的标识。

#2

Try this instead...

试试这个......

SET @TableQuery = 'INSERT INTO @Table(TableName)VALUES(''@TableName'')'

Also you can probably remove

你也可以删除

DECLARE @Table VARCHAR(100)
SET @Table = @ProjectName

since it's not being used.

因为它没有被使用。

#1