Temp table in SQL Server

Question

I used if else statements in the SQL Server stored procedure.

For each, if else, I select the records and save them in a temporary table. But I have to use a temporary table with a different name in each condition.

Is it possible to use the same temp table in each 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

Will 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 do not want to throw it every time, you should try something like this:

 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 a table when the script starts running, and will fall at the end. Values ​​will be inserted into this existing table.

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 

or

 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 ) 

even better

 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;