tsql - 如何对存储过程中的参数进行评估？

Question

我需要执行以下存储过程，其中@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

标签： tsql

Answer 1

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