SQL Stored Proc not allowing temp table to be created

Hi
I'm trying to populate a temp table within a Stored Proc based upon the whether an input parameter is NULL or not but I'm getting an error:
"There is already an object named '#tmp_pmf' in the database."

It seems that the IF condition is not being evaluated correctly and the script is trying to create the temp table twice...?

Appreciate your help.

IF @StockNbr IS NOT NULL
      BEGIN
            PRINT  'Populating filtered #tmp_pmf table';
            BEGIN TRANSACTION
                  SELECT DISTINCT  phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm
                  INTO #tmp_pmf
                  FROM odsMaterials.dbo.lnc_snpsht_pmf
                  WHERE LEFT(stk_nbr,1) = 'A'))
            COMMIT TRANSACTION
      END

      IF @StockNbr IS NULL
      BEGIN
            PRINT  'Populating non-filtered #tmp_pmf table';
            BEGIN TRANSACTION
                  SELECT DISTINCT  phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm
                  INTO #tmp_pmf
                  FROM odsMaterials.dbo.lnc_snpsht_pmf
            COMMIT TRANSACTION
      END
fjkilkenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The parser checks for temp table existence before the code is actually run.  Thus, the parser basically ignores the IFs, and so it "sees":

...
                  INTO #tmp_pmf
...
                  INTO #tmp_pmf

And therefore thinks #tmp_pmf as being created twice, and so flags it as an error.

Since the temp table definition is identical in your specific case, you could do the temp table creation first, then INSERT into the table using the IFs later, like so:


SELECT phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm
INTO #tmp_pmf
FROM odsMaterials.dbo.lnc_snpsht_pmf
WHERE 1 = 0 --just create table structure, don't insert any rows

IF @StockNbr IS NOT NULL
      BEGIN
            PRINT  'Populating filtered #tmp_pmf table';
            BEGIN TRANSACTION
                  INSERT INTO #tmp_pmf
                  SELECT DISTINCT  phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm
                  FROM odsMaterials.dbo.lnc_snpsht_pmf
                  WHERE LEFT(stk_nbr,1) = 'A'))
            COMMIT TRANSACTION
      END
      ELSE
      BEGIN
            PRINT  'Populating non-filtered #tmp_pmf table';
            BEGIN TRANSACTION
                  INSERT INTO #tmp_pmf
                  SELECT DISTINCT  phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm
                  FROM odsMaterials.dbo.lnc_snpsht_pmf
            COMMIT TRANSACTION
      END
0
 
Carl TawnSystems and Integration DeveloperCommented:
First off, why not just use an ELSE rather than two separate IFs?

i.e.
IF @StockNbr IS NOT NULL
      BEGIN
            PRINT  'Populating filtered #tmp_pmf table';
            BEGIN TRANSACTION
                  SELECT DISTINCT  phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm 
                  INTO #tmp_pmf
                  FROM odsMaterials.dbo.lnc_snpsht_pmf
                  WHERE LEFT(stk_nbr,1) = 'A'))
            COMMIT TRANSACTION
      END
      ELSE
      BEGIN
            PRINT  'Populating non-filtered #tmp_pmf table';
            BEGIN TRANSACTION
                  SELECT DISTINCT  phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm 
                  INTO #tmp_pmf
                  FROM odsMaterials.dbo.lnc_snpsht_pmf
            COMMIT TRANSACTION
      END

Open in new window

Secondly, on a side note, your BEGIN/COMMIT transaction statements are redundant. SELECT statements do not modify data, so can't be rolled back - so the transaction control you have is just generating unnecessary overhead.
0
 
fjkilkenAuthor Commented:
thanks, I had previously tried using "ELSE" but it gave me the same error, I copy/pasted your code but still the same error.
It seems to be a very straightforward bit of code but frustrating as to why it is not working.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would try to avoid the temp table altogether, and use table variables

the error is coming likely from the fact that the table is created on first call, but never dropped.

also I would simplify eventually into 1, without the if/else
SELECT DISTINCT  phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm
                  INTO #tmp_pmf
                  FROM odsMaterials.dbo.lnc_snpsht_pmf
                  WHERE ( @StockNbr IS NULL OR LEFT(stk_nbr,1) = 'A'  )   

Open in new window

0
 
David ToddSenior DBACommented:
Hi,

To me, that temp table must be being created in a loop. A good practice is to test for its existence and drop it prior to a select into or create statement.

if object_id( N'tempdb#tmp_pmf', N'U' ) is not null
      drop table #tmp_pmf;

HTH
  David

PS I suggest that instead of a select into, that you do an insert ... select, and prior to the top of the if, check if the table exists. If it does, truncate it. If it doesn't, create it
0
 
Kamal RajConnect With a Mentor DeveloperCommented:
Rather than doing Select into that #tmp_pmftable Create the table and Insert the value into that #tmp_pmf table as below.

if object_id( N'tempdb#tmp_pmf', N'U' ) is not null
 BEGIN
      drop table #tmp_pmf;
END
 CREATE TABLE #tmp_pmf (phy_ste_nm CHAR(10), stk_nbr  CHAR(10), sts_cd CHAR(10) ,s_dtm DATETIME,e_dtm DATETIME)


IF @StockNbr IS NOT NULL
       BEGIN
             PRINT  'Populating filtered #tmp_pmf table';
             BEGIN TRANSACTION
                  Insert  INTO #tmp_pmf  SELECT DISTINCT  phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm
                  FROM odsMaterials.dbo.lnc_snpsht_pmf
                   WHERE LEFT(stk_nbr,1) = 'A'))
             COMMIT TRANSACTION
       END

       IF @StockNbr IS NULL
       BEGIN
             PRINT  'Populating non-filtered #tmp_pmf table';
             BEGIN TRANSACTION
                   Insert into INTO #tmp_pmf SELECT DISTINCT  phy_ste_nm, stk_nbr, sts_cd,s_dtm,e_dtm
                   FROM odsMaterials.dbo.lnc_snpsht_pmf
             COMMIT TRANSACTION
       END
0
 
fjkilkenAuthor Commented:
thanks for the help guys
based upon reading  the various responses, I've since decided to create a physical table and populate it - it's worked out quite well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.