Temp Table in SQL Server

Question

I have used if else statements in my SQL Server stored procedure.

For every if else I am selecting records and storing in temporary table. But I have to use temp table with different name in every condition.

Is it possible to use same temp table in every if else condition?

This is my stored procedure

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

Answer 1

Would this work for you?

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

Or if you don't want to drop it each time, you should try something similar:

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

This will create the table when the script starts running, and will drop it on the end. The values will get inserted to this already existing table.