SQL Server中的临时表

Dipali Wagh 发表于 Dev

迪帕里·瓦格（Dipali Wagh）

我在SQL Server存储过程中使用了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

亚历克斯·萨博

这对您有用吗？

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