Problem with Temp table created in IF block 1

[mbabcock]

I get the following error when checking syntax of my stored proc:

Microsoft SQL-DMO (ODBC SQLState: 42S01)
---------------------------
Error 2714: There is already an object named '#TempReps' in the database.

...but the temp table DOESN'T exist!!!!!!! I checked TempDb and it's not there. I even exit out of EM completely and come back and it still throws this error.

Here's the code from the IF block (modified for brevity here):

if @tcSalesRepID <> '' -- get specific sales rep
select c1.cSalesRepID, c1.iMailCompID
into #TempReps
from CallCampaingResponds c1
inner join dbo.TempMailComp t1
on t1.iMailCompID = c1.iMailCompID
group by c1.cSalesRepID, c1.iMailCompID

else -- get all salesreps for given events
select c1.cSalesRepID, c1.iMailCompID
into #TempReps
from CallCampaingResponds c1
inner join dbo.TempMailComp t1
on t1.iMailCompID = c1.iMailCompID
where c1.csalesrepid = @tcSalesRepID
group by c1.cSalesRepID, c1.iMailCompID

Ideas how to overcome this issue? I had the same problem with TempMailComp and just decided to make a real table temporary (in the same schema) and later delete but that's silly...that defeats the whole purpose of the #TempTable idea!

Ideas?

tia!
--Michael

 

[jbenson001]

You will have to use the solution you used for your TempMailComp table.
When the complier sees the code for the temp table, (created by your select into), it sees that it already exists. The only other way around it is to use a different table name.

Jim

 

[gmmastros]

You should create the temp table first, then insert in to it (instead of select into).

[!]Create Table #TempReps(cSalesRepId integer, iMailCompID Integer)[/!]

if @tcSalesRepID <> ''  -- get specific sales rep
	[!]insert Into #TempReps(cSalesRepId, iMailCompId)[/!]
    select c1.cSalesRepID, c1.iMailCompID
    from CallCampaingResponds c1
    inner join dbo.TempMailComp  t1
    on t1.iMailCompID = c1.iMailCompID
    group by c1.cSalesRepID, c1.iMailCompID

else -- get all salesreps for given events
	[!]insert Into #TempReps(cSalesRepId, iMailCompId)[/!]
    select c1.cSalesRepID, c1.iMailCompID
    from CallCampaingResponds c1
    inner join dbo.TempMailComp  t1
    on t1.iMailCompID = c1.iMailCompID
    where c1.csalesrepid = @tcSalesRepID
    group by c1.cSalesRepID, c1.iMailCompID

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

>> modified for brevity

So... this may not work.

Looking at the 2 queries, the only difference I see is that one has where c1.csalesrepid = @tcSalesRepID

Given this, you could re-write the query to be...

Create Table #TempReps(cSalesRepId integer, iMailCompID Integer)

insert Into #TempReps(cSalesRepId, iMailCompId)
Select c1.cSalesRepID, 
       c1.iMailCompID
from   CallCampaingResponds c1
       inner join dbo.TempMailComp  t1
         on t1.iMailCompID = c1.iMailCompID
where  (@tcSalesRepId = '' Or c1.csalesrepid = @tcSalesRepID)
group by c1.cSalesRepID, c1.iMailCompID

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mbabcock]

George,

The difference in the two queries is this: one gets ALL the records for a given sales rep, whereas the other one only gets a specific sales rep. You're query above gets the specific sales rep or any that match a NULL value....it does NOT satisfy the need to grab ALL of the sales reps should it present itself.

Perhaps this is a good place where I should use dynamic SQL?

--Michael

 

[gmmastros]

No. Don't use dynamic SQL for this.

Let's think about the logic a little, shall we. Basically we have

Where a=b or c=d

If a=b, it doesn't matter what c or d is, the records should be returned. If a <> b, then the values of c and d become important.

So... if your @tcSalesRepId = '' then all the records should be returned. I suspect the problem is that it isn't always an empty string. Try this...

where (IsNull(RTrim(@tcSalesRepId), '') = '' Or c1.csalesrepid = @tcSalesRepID)

so... if @tcSalesRepId is NULL, and empty string, or a string of (any number) of spaces, then all the records should be returned.

Test this in query analyzer to verify that it is working as expected.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

cursors are not always bad....
dynamic sql is sometimes faster than WHERE y1 = COALESCE(@p1, y1)

read the following

http://blogs.technet.com/wardpond/archive/2006/07/10/441050.aspx

scroll down to the comment that starts with "I hope you're joking, Ward? "



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mbabcock]

George,

Looks like your idea originally is going to work (the one where I create the Temp Cursor and then just do an INSERT INTO ...SELECT SQL.

Thanks!
--Michael