代码之家 › 专栏 › 技术社区 › Gregory Suvalian

如何对存储过程中的参数进行求值?

tsql

Gregory Suvalian · 技术社区 · 4 年前

我需要执行以下存储过程 @Table_temp 应该是一系列 @Table 和 temp 那么,我如何正确地将其参数化呢?

CREATE PROCEDURE [dbo].[swap_Collect]
@Table  varchar,
@UniqueColumn varchar
AS

BEGIN
    IF (OBJECT_ID(@Table) IS NOT NULL )
    BEGIN
        DECLARE @Table_temp as varchar 
        SET @Table_temp= @Table + '_temp'
      DELETE FROM @Table where @UniqueColumn in (Select @UniqueColumn from @Table_temp)
      INSERT INTO @Table SELECT * from PRODUCTS_temp
      SELECT 1
    END
ELSE
    BEGIN 
      SELECT * INTO @Table from Collects_temp
      SELECT 1
    END
END

1 回复 | 直到 4 年前

Eric Brandt 4 年前

尽管有几种SQL方言支持,但SQL Server不支持变量作为对象名。你必须使用动态SQL来解决这个问题。

发表评论 EXEC 行和取消注释 PRINT 验证动态SQL的行正在生成相应的语句。

我也注意到,你所有的 varchar 声明缺少长度参数。这让他们都 one character long ,这当然不是你的本意。表名是通过以下方式定义的 sysname ,这几乎与 NVARCHAR(128) ,所以我切换到了那个。

CREATE PROCEDURE [dbo].[swap_Collect]
  @Table  varchar,
  @UniqueColumn varchar
AS

-- Moving the variable declaration so we can use them in both the IF and the ELSE
DECLARE @Table_temp as varchar = @Table + '_temp'
       ,@SQL varchar(500)
       ,@NL varchar(2) = CONCAT(CHAR(10),CHAR(13)); 
        -- This is a new line character to make the dynamic code more readable.

BEGIN
  IF (OBJECT_ID(@Table) IS NOT NULL ) --The variable is fine here.
    BEGIN

      -- Delete from the base table here.      
      SET @SQL = CONCAT(
        'DELETE FROM ',QUOTENAME(@Table), @NL,
        'WHERE ',QUOTENAME(@UniqueColumn),
        ' IN (SELECT ',QUOTENAME(@UniqueColumn),' FROM ', QUOTENAME(@Table_temp),');');
      --PRINT @SQL;
      EXEC sp_executesql @SQL;

      -- Insert into the base table here.
      SET @SQL = CONCAT(
        'INSERT INTO ', QUOTENAME(@Table), @NL,
        'SELECT * FROM ', QUOTENAME(@Table_temp),';');
      --PRINT @SQL;
      EXEC sp_executesql @SQL;

      SELECT 1
    END
  ELSE
      BEGIN 
        -- Insert into the base table here.
        -- SELECT...INTO only works if the target table doesn't exist. Since @Table does exist
        -- we'll use INSERT INTO instead. 
        SET @SQL = CONCAT(
          'INSERT INTO ', QUOTENAME(@Table), @NL,
          'SELECT * FROM ', QUOTENAME(@Table_temp),';');
        --PRINT @SQL;
        EXEC sp_executesql @SQL;

        SELECT 1
      END
END

既然这里使用了它,这里有一个很好的参考。 Dos and Don'ts of Dynamic SQL

Gregory Suvalian 4 年前

ALTER PROCEDURE [dbo].swap_Collect(
@Table as varchar(MAX),
@UniqueColumn as varchar(MAX))
AS
BEGIN
    IF (OBJECT_ID(@Table) IS NOT NULL )
    BEGIN

       DECLARE @query nvarchar(MAX)
       set @query = 'DELETE FROM '+  @Table + ' where ' + @UniqueColumn  + ' in (Select ' +  @UniqueColumn + ' from ' + @Table +  '_temp)' 
       set @query = @query + 'INSERT INTO ' + @Table + ' SELECT * from ' +  @Table + '_temp'
       EXEC sp_executesql @query
       SELECT 1
    END
    ELSE
    BEGIN 
       set @query = 'SELECT * INTO ' + @Table + ' from ' + @Table + '_temp'
       print @query
       EXEC sp_executesql @query
       SELECT 1
     END
END