Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Stored Proc not allowing temp table to be created

Posted on 2014-04-30
7
Medium Priority
?
291 Views
Last Modified: 2014-05-13
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
0
Comment
Question by:fjkilken
7 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40031442
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
 

Author Comment

by:fjkilken
ID: 40031455
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40031456
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 35

Expert Comment

by:David Todd
ID: 40031462
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 750 total points
ID: 40032683
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
 

Assisted Solution

by:Kamal Raj
Kamal Raj earned 750 total points
ID: 40061416
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
 

Author Closing Comment

by:fjkilken
ID: 40061592
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question