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

General SQL Server Forums

New to SQL Server Programming

conditional query

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 03:51:10

Hi again. Another problem though...
Can I make a query in if else statement? For example
int querynum;
if(querynum==1)<execute query1>
else if(querynum==2)<execute query2>
else <execute query3>

There is no problem with passing parameters(getquerynum) from my front end application to sql function. I can manage to do it. hehe.

the query looks like this:

DECLARE @querynum number
SET @querynum = @getquerynum

what i want to do is,

if(querynum==1) Select * from hremployees
else if(querynum==2) Select * from hrapplicants
else Select * from pspersonaldata

is it possible by having multiple queries in conditional statement?
if so, how to do it in sql language?
Thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 04:00:32

use single equal = instead of double equal == sign for comparison

"is it possible by having multiple queries in conditional statement?"
yes. enclose the query in BEGIN ... END block

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 04:12:11

This is whta i did.

DECLARE @querynum int
SET @querynum = 2

if(@querynum=1) BEGIN Select * from hremployees END
else if(@querynum=2) BEGIN Select * from hrapplicants END
else BEGIN Select * from pspersonaldata END

Oh thanks KH. It works well.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 04:23:17

What are you trying to achieve here ? A common stored procedure for querying data from various table ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-07 : 04:37:30

DECLARE @querynum int
SET @querynum = 2

if @querynum = 1
Select * from hremployees

if @querynum = 2
Select * from hrapplicants

if @querynum not in (1, 2)
Select * from pspersonaldata

Peter Larsson
Helsingborg, Sweden

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 04:44:20

no. same table as well. but the queries have a distinct parameters.
for example.

DECLARE @querynum int
SET @querynum = 2

if(@querynum=1) BEGIN Select * from hremployees where empdcno = 12 END
else if(@querynum=2) BEGIN Select * from hremployees inner join CSVTable.... END
else BEGIN Select * from hremployees END

it looks like this. My intention for this is to prevent of creating another report design. It must be 1 report only for hremployees. However, if you create multiple reports for hremployeesis,hmmmmm... its not logically correct. that's on my own opinion.

Thanks again.
-Ron-

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 22:02:47

what is wrong with my query? did i proper use the if statement?

DECLARE @querynum int
SET @querynum = 2
DECLARE @religion varchar (100)
SET @religion = 'Born again, adventist'
select *
from
(
select a.HospName,a.HospLogo,a.HospTelephone,a.HospWebsite,a.HospStreetBldg1,a.HospAddress as Address
from appsysconfiggeneral as a
) a
cross join
(
if(@querynum = 1)
BEGIN
select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
inner join CSVTable(@religion) as R on P.religion = R.stringval
END
) b

ERROR:
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 22:21:20

You can't have the IF condition inside a query. What are you trying to achieve here ?

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 22:29:46

this is what i want to achieve.

DECLARE @religion varchar (100), @querynum int
SET @religion = 'Born again, adventist'
SET @querynum = 2
select *
from
(
select a.HospName,a.HospLogo,a.HospTelephone,a.HospWebsite,a.HospStreetBldg1,a.HospAddress as Address
from appsysconfiggeneral as a
) a
cross join
(
if(@querynum = 1)
BEGIN
select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
inner join CSVTable(@religion) as R on P.religion = R.stringval
END
else
begin
select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
inner join CSVTable(@religion) as R on P.religion = R.stringval
end
) b

But it pretty works fine here.
DECLARE @religion varchar (100), @querynum int
SET @religion = @empreligion
SET @querynum = @getquerynum

if(@querynum =1) begin
select *
from
(
select a.HospName,a.HospLogo,a.HospTelephone,a.HospWebsite,a.HospStreetBldg1,a.HospAddress as Address
from appsysconfiggeneral as a
) a
cross join
(
select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
inner join CSVTable(@religion) as R on P.religion = R.stringval
) b
end
else
begin
select *
from
(
select a.HospName,a.HospLogo,a.HospTelephone,a.HospWebsite,a.HospStreetBldg1,a.HospAddress as Address
from appsysconfiggeneral as a
) a
cross join
(
select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
) b
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 01:35:56

Your CSV variable should not be divided by both comma and space!

Peter Larsson
Helsingborg, Sweden

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-08 : 01:41:40

"Your CSV variable should not be divided by both comma and space!"

Sorry I can't get your point. What do you mean by that?

Thanks.
-Ron-

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 03:46:43

If you split the value "Born again, adventist", you get two records in the split function like this
"Born again"
" adventist" -- Please notice the space in front of adventist

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 03:47:03

And there are no records matching that one!

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 03:53:13

Actually the Doctor's CSVTable has kindly remove the spaces


select	stringval = '[' + stringval + ']'
from	CSVTable('   Born again  ,     adventist     ')
/*
stringval                                                                                              
-------------
[Born again]
[adventist]
*/

from CSVTable code

quote:


insert into @t
values (CASE WHEN isnumeric(@c)=1 THEN @c else Null END,
rtrim(ltrim(@c)),

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-08 : 05:01:17

Even with space or no, still works perfectly... :) :) :)
Thanks to KH.

-Ron-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources