query logic - SQL Server Forums

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

SQL Server Development (2000)

query logic

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2006-08-21 : 15:02:59

I have to do a search query like this:
select * from table1 where....
If @@rowcount=0
Begin
select * from table2 where....
ENd
My issue is when I execute part 2 (after rowcount=0) I am always is getting part 1 as an empty result? How do I fix my query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 15:05:36

IF EXISTS (select 1 from mytable1 where...)
select * from mytable1 where...
else
select * from mytable2 where...

Peter Larsson
Helsingborg, Sweden

sardinka
Posting Yak Master

142 Posts

Posted - 2006-08-21 : 15:08:36

Since I have a huge tables(more then 1 mln rows) is it some how I can ellumintate this from your query: IF EXISTS (select 1 from mytable1 where...)???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 15:09:56

Exist exits when first record is found, not all of them. So it is very fast.
And besides, this is the fastest way to acomplish your task.

Read about EXISTS in Books Online.

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-21 : 15:18:13

You actually want IF EXISTS (SELECT * FROM ...)

IF EXISTS and IF NOT EXISTS are the only times when SELECT * should be used. It actually is faster than SELECT 1 for some reason.

Tara Kizer

sardinka
Posting Yak Master

142 Posts

Posted - 2006-08-21 : 15:27:57

So there is no way I can get read of using twice select * from table1?
Right?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 01:01:08

Yes, there is another way.

SET NOCOUNT ON

SELECT * INTO #Temp FROM MyTable1 WHERE ...

IF EXISTS (SELECT * FROM #Temp)
    SELECT * FROM #Temp
ELSE
    SELECT * FROM MyTable2 WHERE ...

DROP TABLE #Temp

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-22 : 02:54:10

quote:
Originally posted by sardinka

I have to do a search query like this:
select * from table1 where 1 = 2
If @@rowcount=0
Begin
select * from table2 where....
ENd
My issue is when I execute part 2 (after rowcount=0) I am always is getting part 1 as an empty result? How do I fix my query?

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 04:00:47

Mahesh, that will get two resultset of which the first is always empty.

Of what I can read from the post, he only wants to retreive the second resultset if there are no records in the first, and if so, he does not want to get the first resultset at all.

Peter Larsson
Helsingborg, Sweden

LazyDragon
Starting Member

30 Posts

Posted - 2006-08-22 : 05:36:23

quote:
Originally posted by tkizer

You actually want IF EXISTS (SELECT * FROM ...)

IF EXISTS and IF NOT EXISTS are the only times when SELECT * should be used. It actually is faster than SELECT 1 for some reason.

Here is a link to a recent discussion in that topic, which states SELECT 1 is better than SELECT * in EXISTS

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16097

LazyDragon
T-SQL Programmer

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-22 : 08:24:09

quote:
Originally posted by Peso

Mahesh, that will get two resultset of which the first is always empty.

Of what I can read from the post, he only wants to retreive the second resultset if there are no records in the first, and if so, he does not want to get the first resultset at all.

Peter Larsson
Helsingborg, Sweden

Thanks PESO,
Mahesh

sardinka
Posting Yak Master

142 Posts

Posted - 2006-08-22 : 10:50:27

What I wanted if my first result retrives some data don't execute the second part. and If no result from part 1 execute part 2.
But when part 2 is executed, return only part 2 (and ignore empty data from part 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 11:45:27

Then use this code

SET NOCOUNT ON

SELECT * INTO #Temp FROM MyTable1 WHERE ...

IF EXISTS (SELECT * FROM #Temp)
    SELECT * FROM #Temp
ELSE
    SELECT * FROM MyTable2 WHERE ...

DROP TABLE #Temp

or this code

IF EXISTS (select 1 from mytable1 where...)
select * from mytable1 where...
else
select * from mytable2 where...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources