Get only one SELECT statement to run

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)

Get only one SELECT statement to run

Author

Topic

jerrypaz
Starting Member

18 Posts

Posted - 2004-10-28 : 13:19:58

I have two SELECT statements, but only want one to run based on one SELECT return no results (false), and the other SELECT returning information (true). Below are my two SELECT statements, how do I make the two statements run based on results true or false (in this case, the first statement is false, the second is true)?

(SELECT gr [Group], org Organization, sq Squadron, officeDesc Office, officeNum Extension FROM
dbo.tblOrg, dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE
dbo.tblOrg.orgID = dbo.tblOffice.orgID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblOffice.officeDesc LIKE 'exec' + '%')

(SELECT gr [Group], sq Squadron, officeDesc Office, officeNum Extension FROM
dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblOffice.officeDesc LIKE 'Exec' + '%')

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 13:23:05

execute first

if @@rowcout = 0
begin
execute second
end

Go with the flow & have fun! Else fight the flow

jerrypaz
Starting Member

18 Posts

Posted - 2004-10-28 : 13:32:43

quote:
Originally posted by spirit1

execute first

if @@rowcout = 0
begin
execute second
end

Go with the flow & have fun! Else fight the flow

OK, I kind of see what you're saying, but with the execute statements, do I word it just like you said, or is that an example? I've never done execute statements.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 13:35:29

you don't need to use Exec sataments

use:


SELECT gr [Group], org Organization, sq Squadron, officeDesc Office, officeNum Extension 
FROM   dbo.tblOrg, dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE  dbo.tblOrg.orgID = dbo.tblOffice.orgID AND
       dbo.tblSq.sqID = dbo.tblOffice.sqID AND
       dbo.tblSq.grID = dbo.tblGroup.grID AND
       dbo.tblOffice.officeDesc LIKE 'exec' + '%'

if @@rowcout = 0
begin
SELECT gr [Group], sq Squadron, officeDesc Office, officeNum Extension 
FROM   dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE  dbo.tblSq.grID = dbo.tblGroup.grID AND
       dbo.tblSq.sqID = dbo.tblOffice.sqID AND
       dbo.tblOffice.officeDesc LIKE 'Exec' + '%'
END

Go with the flow & have fun! Else fight the flow

jerrypaz
Starting Member

18 Posts

Posted - 2004-10-28 : 13:41:12

How do I prevent the first statement from displaying headers with no data?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 13:50:43

i guess this is the only way. since you want to do the 2nd search if the first one yields no result...


if not exists(SELECT gr [Group], org Organization, sq Squadron, officeDesc Office, officeNum Extension 
FROM   dbo.tblOrg, dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE  dbo.tblOrg.orgID = dbo.tblOffice.orgID AND
       dbo.tblSq.sqID = dbo.tblOffice.sqID AND
       dbo.tblSq.grID = dbo.tblGroup.grID AND
       dbo.tblOffice.officeDesc LIKE 'exec' + '%')
begin
SELECT gr [Group], sq Squadron, officeDesc Office, officeNum Extension 
FROM   dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE  dbo.tblSq.grID = dbo.tblGroup.grID AND
       dbo.tblSq.sqID = dbo.tblOffice.sqID AND
       dbo.tblOffice.officeDesc LIKE 'Exec' + '%'
END
else
begin 
SELECT gr [Group], org Organization, sq Squadron, officeDesc Office, officeNum Extension 
FROM   dbo.tblOrg, dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE  dbo.tblOrg.orgID = dbo.tblOffice.orgID AND
       dbo.tblSq.sqID = dbo.tblOffice.sqID AND
       dbo.tblSq.grID = dbo.tblGroup.grID AND
       dbo.tblOffice.officeDesc LIKE 'exec' + '%'
end

or you could put the first search results into a temp table and see if it has count(*) > 0
if yes then do select * from #temp
elserun second select.
this should probably be faster... you'll have to test it to be sure.

Go with the flow & have fun! Else fight the flow

jerrypaz
Starting Member

18 Posts

Posted - 2004-10-28 : 13:58:11

That worked GREAT. Thanks.

The SQL books I have don't talk about if-then-else statements or any programming such as this. I know about them, because I am a novice programmer, but I didn't know you could use them is SQL Query Analyser. Do you have any suggestions on books or web site that talks more about this?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 14:02:23

http://www.sqlteam.com/store.asp

i guess you'll just need practice like all of us

its just like this:
since exists retruns 1 or 0 it can be used in if else....
you just need to learn the stuff and then put them logically together.

Go with the flow & have fun! Else fight the flow

jerrypaz
Starting Member

18 Posts

Posted - 2004-10-28 : 14:05:01

OK, THANKS again.

jerrypaz
Starting Member

18 Posts

Posted - 2004-10-28 : 15:41:10

Does this look right to you? If not, what needs to be changed?

CREATE PROCEDURE [dbo].[QueryDesc]
(
@Desc varchar
)
AS
if exists
(SELECT gr [Group], org Organization, sq Squadron, officeDesc Office, officeNum Extension
FROM dbo.tblOrg, dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE dbo.tblOrg.orgID = dbo.tblOffice.orgID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%')
begin
SELECT gr [Group], org Organization, sq Squadron, officeDesc Office, officeNum Extension
FROM dbo.tblOrg, dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE
dbo.tblOrg.orgID = dbo.tblOffice.orgID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%'
END
else if exists
(SELECT gr [Group], sq Squadron, officeDesc Office, officeNum Extension
FROM dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%')
begin
SELECT gr [Group], sq Squadron, officeDesc Office, officeNum Extension
FROM dbo.tblOffice, dbo.tblGroup, dbo.tblSq
WHERE
dbo.tblSq.grID = dbo.tblGroup.grID AND
dbo.tblSq.sqID = dbo.tblOffice.sqID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%'
END
else
begin
SELECT gr [Group], officeDesc Office, officeNum Extension
FROM dbo.tblOffice, dbo.tblGroup
WHERE
dbo.tblOffice.grID = dbo.tblGroup.grID AND
dbo.tblOffice.officeDesc LIKE '%' + '@desc' + '%'
END
GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-28 : 22:56:12

duplicate?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41907

--------------------
keeping it simple...

jerrypaz
Starting Member

18 Posts

Posted - 2004-10-28 : 22:58:01

Yup. Nobody was responding to the other one, so I created a new thread.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-29 : 04:39:16

looks ok... maybe you should try it with temp tables to see if it's faster...

Go with the flow & have fun! Else fight the flow