Solved

SQL Stored Proc not allowing temp table to be created

Posted on 2014-04-30
7
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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:
Scott Pletcher 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

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…
In this article I will describe the Detach & Attach 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.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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