Social Icons

Pages

Thursday, April 3, 2014

How to use the same temporary table twice in a stored procedure

Let's make an example
you  want to use a temporary table in the same Stored Procedure based on different Condition, as below:

IF(Condition)
BEGIN
select something  & Insert into #TempTable
where condition
END
ELSE
BEGIN
select something  & Insert into #TempTable
where condition
END

If you try to compile this kind of code, it will return an error like 'There is already an object named #myTempTable in the database'. SQL doesn't allow you to use the ‘into #TempTable’ twice in the same Stored Procedure.

So the simplest way is, create the temporary table first, then make the if-else condition
DECLARE @VAL INT=1;

--Create temp Table
select * into #Temp  from dbo.tblCountries where 0=1

 --insert records to temp table
IF(@VAL=1)
      BEGIN
            INSERT INTO #Temp
            select *
            FROM dbo.tblCountries
            --Where
      END
ELSE
      BEGIN
            INSERT INTO #Temp
            select *
            FROM dbo.tblCountries
            --Where
      END

DROP TABLE #Temp