IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHE

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2004-02-04 : 19:18:03

Hello everyone!

I've written an s_proc that is used to gather data for a crystal report. There are 6 different scenarios that could occur when a user tries to get data to populate the report. Certain people will get some data, others will get more, and yet others will get it all. This has been working without issue for nearly a year. My conundrum is that it now needs to change and I need to obtain additional information from another table which cannot be included in the join of the original code. My solution was to create a #table with the first query and then update the #table with info from the second query. This works without issue when there are no ifs, elses, etc... however, when I put in all 6 scenarios I get the following error:

Server: Msg 2714, Level 16, State 2, Procedure NTS_RPT_MANAGEMENT_ACCTS_ACH_DISBURSEMENT, Line 1
There is already an object named '#mgmntACH' in the database.

I only get it 5 times though?? It's as though it's only recognizing one drop command.

Here's a sample of what I'm trying to do

create procedure get_info 
                 @wUser as varchar(3)
                 as 


if @wUser = 'DAN'
  begin
    select lname as Lname,
           addr as Laddr
      into #info
    from yada

    insert #info
      select fname as Lname,
             addr2 as laddr
        from yada2

    select * from #info
    IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'TEMPDB.[DBO].[#info]')and OBJECTPROPERTY(id, N'IsTable') = 1)
    DROP TABLE TEMPDB.[DBO].[#info]
  end

if @wUser = 'TJB'
  begin
    select lname as Lname,
           addr as Laddr,
           city as lcity,
      into #info
    from yada

    insert #info
      select fname as Lname,
             addr2 as laddr,
             city 2 as lcity
        from yada2

    select * from #info
    IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'TEMPDB.[DBO].[#info]')and OBJECTPROPERTY(id, N'IsTable') = 1)
    DROP TABLE TEMPDB.[DBO].[#info]
  end

Does anyone have any ideas?

Thanks!
Teresa

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-04 : 19:36:27

You can't create a temp table twice within the same batch.
When the SP compiles it will resolve the objects including the columns from the create of the temp table - which means it can't cope with two different definitions.
And you shouldn't need to check for the existence of a temp table
For what you have here you could just use a union and not bother with temp tables.

Try something more like

-- this is better as a create table - make sure the fields are nullable
select lname as Lname,
addr as Laddr,
city as lcity,
into #info
from yada
where 1=0

if @wUser = 'DAN'
begin
insert #info
select lname as Lname,
addr as Laddr
from yada

insert #info
select fname as Lname,
addr2 as laddr
from yada2

select Lname, laddr from #info

if @wUser = 'TJB'
begin
insert #info
select lname as Lname,
addr as Laddr,
city as lcity,
from yada

insert #info
select fname as Lname,
addr2 as laddr,
city 2 as lcity
from yada2

select * from #info

Or better use different tables

if @wUser = 'DAN'
begin
select lname as Lname,
addr as Laddr
into #info1
from yada

insert #info1
select fname as Lname,
addr2 as laddr
from yada2

select * from #info1
drop table #info1
end

if @wUser = 'TJB'
begin
select lname as Lname,
addr as Laddr,
city as lcity,
into #info2
from yada

insert #info2
select fname as Lname,
addr2 as laddr,
city 2 as lcity
from yada2

select * from #info
drop table info2
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-04 : 20:10:34

Nigel mentioned a UNION... Would something like the following be useful in your situation:

select 	lname, 
	addr, 
	case when @wUser in ('TJB') then city else null end city
from
(
	select lname,addr,city 
	from yada
	union all
	select fname,addr2,city2 
	from  yada2
) d

TJ
Posting Yak Master

201 Posts

Posted - 2004-02-04 : 20:34:56

quote:
Originally posted by ehorn

Nigel mentioned a UNION... Would something like the following be useful in your situation:
select lname,
addr,
case when @wUser in ('TJB') then city else null end city
from
(
select lname,addr,city
from yada
union all
select fname,addr2,city2
from yada2
) d

I'll work with this and see. I forgot to mention, (please forgive me for that!) that the first query uses a date range on specific column of data from the first table and the second query uses a date range on a different column from the second table. I'll try to give the dates a temp name to normalize them and see if that works.

Thanks for your help ehorn & NR! You're the best!

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources