SQL Server中的临时表

Question

我在SQL Server存储过程中使用了if else语句。

对于每个if else，我都会选择记录并将其存储在临时表中。但是我必须在每个条件中使用具有不同名称的临时表。

是否可能在每个if else条件中使用相同的临时表？

这是我的存储过程：

alter procedure GetRecords
    @Id int ,
    @status varchar(10),
    @EmpId int,
    @PageIndex INT = 1,
    @PageSize INT = 10,
    @RecordCount INT OUTPUT
as
begin
    if(@Id = 1)
    begin
        select ROW_NUMBER() OVER  (ORDER BY Id  desc)AS RowNumber,Id,dDateTime,[Status],Stage 
        INTO #Results  from DiscrepencyMaster     where [Status]=@status

        SELECT @RecordCount = COUNT(*)
        FROM #Results

        SELECT * FROM #Results
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    end
    else if (@Id = 2)
    begin
        select ROW_NUMBER() OVER  (ORDER BY Id  desc)AS RowNumber,Id,dDateTime,[Status],Stage 
        INTO #Results1  from DiscrepencyMaster where EmpId=@EmpId

        SELECT @RecordCount = COUNT(*)
        FROM #Results1

        SELECT * FROM #Results1
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    end 
end

- Dipali Wagh

---

为什么你不试试看呢？我不明白为什么不可以。顺便提一下，不要数表中的记录，直接使用 @@ROWCOUNT。 - Nick.McDermaid

ElectricLlama是什么？如果我不使用@recordcount也没有问题。我想使用同一个临时表来插入两次记录。 - Dipali Wagh

是的，我也尝试过使用“drop table”。但我的问题是，我无法在if和else两个条件中同时使用“#Result”。 - Dipali Wagh

用 SET @RecordCount = @@ROWCOUNT 替换 SELECT @RecordCount = COUNT(*) FROM #Results。这是一个好习惯... @@ROWCOUNT 始终包含上一条语句中受影响的记录数，这意味着您不必计算表中的记录数。 - Nick.McDermaid

当你使用 select ... into #TempTable 时，你正在创建该表。根据文档，“如果在单个存储过程或批处理中创建了多个临时表，则它们必须具有不同的名称。” 因此，如果你要创建多个不同名称的临时表，你可以先创建表，然后使用 insert into #TempTable ...。注意不要改变原来的意思。 - Mikael Eriksson

Answer 1

这对你有用吗？

alter procedure GetRecords
    @Id int ,
    @status varchar(10),
    @EmpId int,
    @PageIndex INT = 1,
    @PageSize INT = 10,
    @RecordCount INT OUTPUT
as
begin

        IF OBJECT_ID('tempdb..#Results') IS NOT NULL
        DROP TABLE #Results



    if(@Id = 1)
    begin 

        IF OBJECT_ID('tempdb..#Results') IS NOT NULL
        DROP TABLE #Results


        INSERT INTO #Results
        select ROW_NUMBER() OVER  (ORDER BY Id  desc)AS RowNumber,Id,dDateTime,[Status],Stage 
        from DiscrepencyMaster     where [Status]=@status


        SELECT @RecordCount = @@ROWCOUNT


        SELECT * FROM #Results
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1


    end
    else if (@Id = 2)
    begin

        IF OBJECT_ID('tempdb..#Results') IS NOT NULL
        DROP TABLE #Results


        INSERT INTO #Results    
        select ROW_NUMBER() OVER  (ORDER BY Id  desc)AS RowNumber,Id,dDateTime,[Status],Stage 
        from DiscrepencyMaster where EmpId=@EmpId


        SELECT @RecordCount = @@ROWCOUNT


        SELECT * FROM #Results
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1


    end 

DROP TABLE #Results
end

如果您不想每次都删除它，可以尝试类似的方法：

alter procedure GetRecords
    @Id int ,
    @status varchar(10),
    @EmpId int,
    @PageIndex INT = 1,
    @PageSize INT = 10,
    @RecordCount INT OUTPUT
as
begin
        CREATE TABLE #Results
    (
        RowNumber           INT,
        Id                  INT,    
        dDateTime           Datetime,
        Status              varchar(max),
        stage               varchar(max)
    )
        SET IDENTITY_INSERT #Results ON

    if(@Id = 1)
    begin 

        INSERT INTO #Results
        select ROW_NUMBER() OVER  (ORDER BY Id  desc)AS RowNumber,Id,dDateTime,[Status],Stage 
        from DiscrepencyMaster     where [Status]=@status

        SELECT @RecordCount = @@ROWCOUNT

        SELECT * FROM #Results
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    end
    else if (@Id = 2)
    begin

        INSERT INTO #Results    
        select ROW_NUMBER() OVER  (ORDER BY Id  desc)AS RowNumber,Id,dDateTime,[Status],Stage 
        from DiscrepencyMaster where EmpId=@EmpId

        SELECT @RecordCount = @@ROWCOUNT

        SELECT * FROM #Results
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    end 

DROP TABLE #Results
end

脚本启动时将创建表格，并在结束时删除它。 值将插入到此已存在的表中。

- Alex Szabo

---

Alex，我尝试了第一种解决方案。但是它报错了：数据库中已经存在一个名为“#Results”的对象。 - Dipali Wagh

Alex，第二个解决方案出现错误：只有在使用列列表并且启用IDENTITY_INSERT时才能为表“＃Results”的标识列指定显式值。 - Dipali Wagh

@DipaliWagh，您能否再尝试一下第一个解决方案？ - Alex Szabo

解决第二个错误：只需在 #Result 上添加 Set Identity_Insert。 - Ubiquitous Developers

@DipaliWagh，我认为你明白他的意思了。在IF ELSE之外创建一个临时表，然后插入数据即可。其余部分应该是不言自明的。 - Steve

@Alex 的第二个解决方案对我很有帮助。谢谢。 - Dipali Wagh

Answer 2

begin
    IF OBJECT_ID('tempdb..#Results') IS NOT NULL
        DROP TABLE #Results
    CREATE TABLE #Results (rowNum INT, ID INT, status DateTime, stage ?) 
    if(@Id = 1)
    begin
        insert into #Results (rowNum, ID, status, stage)
        select ROW_NUMBER() OVER  (ORDER BY Id  desc) AS RowNumber
              , Id, dDateTime[Status], Stage 
          from DiscrepencyMaster     
         where [Status]=@status

        SELECT @RecordCount = COUNT(*)
        FROM #Results

        SELECT * FROM #Results
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    end
    else if (@Id = 2)
    begin
        insert into #Results (rowNum, ID, status, stage)
        select ROW_NUMBER() OVER  (ORDER BY Id  desc)AS RowNumber,Id,dDateTime,[Status],Stage 
        from DiscrepencyMaster 
        where EmpId=@EmpId

        SELECT @RecordCount = COUNT(*)
        FROM #Results

        SELECT * FROM #Results
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    end
    IF OBJECT_ID('tempdb..#Results') IS NOT NULL
        DROP TABLE #Results 
end

或者

insert into #Results (rowNum, ID, status, stage)
select ROW_NUMBER() OVER  (ORDER BY Id  desc) AS RowNumber
     , Id, dDateTime[Status], Stage 
  from DiscrepencyMaster     
 where (@Id = 1 and [Status]=@status) 
    or (@Id = 2 and EmpId=@EmpId)

更好的是

select count(*) 
  from DiscrepencyMaster     
 where (@Id = 1 and [Status]=@status)
    or (@Id = 2 and EmpId=@EmpId);
select Id, dDateTime[Status], Stage
  from DiscrepencyMaster     
 where (@Id = 1 and [Status]=@status)
    or (@Id = 2 and EmpId=@EmpId); 
 order by ID
OFFSET (@PageIndex -1) * @PageSize + 1 ROWS 
 FETCH NEXT @PageSize ROWS ONLY; 

- paparazzo