Solved

SQL Stored Proc not allowing temp table to be created

Posted on 2014-04-30
7
277 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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 69

Accepted Solution

by:
ScottPletcher earned 250 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 250 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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 …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now