?
Solved

SQL Stored Proc not allowing temp table to be created

Posted on 2014-04-30
7
Medium Priority
?
287 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

752 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