please help with where clause variable

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)

please help with where clause variable

Author

Topic

cr488
Starting Member

23 Posts

Posted - 2006-04-04 : 01:56:48

Hi,

I am trying to put a where clause into variable and I don't know if I can do it.

declare table @state (state char(2), group char(20), state_id char (20))

State group state_id
CA no1 CA
GA no2 noneCA
AR no3 noneCA
DC no3 noneCA
MA no3 noneCA

select state from @state where group like {state_id}
select state from @state where state_id like {group}

actually it is a simple example, my query is much more complicated. What I want to do is that I want to combine where clause to one where clause. If I use option 1, then use 'group like {state_id}' as my where clause, otherwise use 'state_id like {group}' as where clause.

is there anyway to write that way? Thanks!

I write it, but I know it won't work:

Example

if {option} = 1
begin
declare @whereclause char(250)
set @whereclause = [group like {state_id}]
end
else
begin
declare @whereclause char(250)
set @whereclause = [state_id like {group}]
end

select state from @state where @whereclause

I am keeping geting the error message that @whereclause must be a unique value, but if I use a diffrent name, I can not do it the way
I want...

Your help will be highly appreciated!!

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-04-04 : 02:10:13

hi,
Declare @whereclause char(250) once . before an if statement.

cr488
Starting Member

23 Posts

Posted - 2006-04-04 : 02:16:48

Thanks for your reply. It does help. But I encountered another problem, I got an error message saying that 'group like 'noneCA' is an invalid column name....

Is there anyway I can use the statement :

select state from @state where @whereclause

Please advise... Thanks!!!

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-04-04 : 02:25:50

your syntax corrected is below, though I think you should post specific details about your task. I question your model.

declare @State table (State char(2), StateGroup varchar(20), StateID varchar(20))
insert into @State
select 'CA', 'no1', 'CA' union
select 'GA', 'no2', 'noneCA' union
select 'AR', 'no3', 'noneCA' union
select 'DC', 'no3', 'noneCA' union
select 'MA', 'no3', 'noneCA'

declare @Option int,
@StateID varchar(20),
@StateGroup varchar(20)

select @Option = 2,
@StateID = 'CA',
@StateGroup = 'no2'

if @Option = 1
begin
select State
from @State
where StateID = @StateID
end else
begin
select State
from @State
where StateGroup = @StateGroup
end

Nathan Skerl

cr488
Starting Member

23 Posts

Posted - 2006-04-04 : 03:03:26

My task is using either state or group to be my search parameters.

What I am trying to do is that:

if option = 1
then use state as one of my search parameter with 3 condition:
1. State_id = 'CA'
or
2. state_id = 'NoneCA'
or
3. All state

if option = 2
then use group as one of my search parameter with 4 condition :
1. group =no1 with state in ('CA', ........)
or
2. group = no2 with state in ('GA', .......)
or
3. group = no3 with state in ('AR',......)
or.
no group with all states

I am trying to put everthing in one whereclause, I did this in non sql server applications before. But I don't know how to do this in sql server. I was told it is doable and very simple, but I could not firgue out how to do it...

I can not do it in a two seperate select statement as you suggested:

if @Option = 1
begin
select State
from @State
where StateID = @StateID
end else
begin
select State
from @State
where StateGroup = @StateGroup
end

Because my select statement was much large than this with a lot of joins, table and conditions....

From efficiency point, I can not do it.. That is is why I am thinking to put where clause in a variable... Any suggestion? Thanks!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-04 : 04:16:09

quote:
Originally posted by cr488
Is there anyway I can use the statement :

select state from @state where @whereclause

Yes. Dynamic SQL. Check out exec() or sp_executesql

KH
Choice is an illusion, created between those with power, and those without.

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-04-04 : 12:48:46

If you can manage the @Option switch outside the proc then you can manage this select by passing in a non-null value in the desired filter:


declare @State table (State char(2), StateGroup varchar(20), StateID varchar(20))
insert into @State
select 'CA', 'no1', 'CA' union
select 'GA', 'no2', 'noneCA' union
select 'AR', 'no3', 'noneCA' union
select 'DC', 'no3', 'noneCA' union
select 'MA', 'no3', 'noneCA'


declare @StateID varchar(20), 
	@StateGroup varchar(20)

select 	@StateID = 'CA',
	@StateGroup = null --'no2'

select State
from @State
where StateID = coalesce(@StateID, StateID) 
	and StateGroup = coalesce(@StateGroup,StateGroup)

Passing both variables as NULL would return all records.

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-04-04 : 13:51:05

If you cannot manage the switch, maybe case like so:

declare @State table (State char(2), StateGroup varchar(20), StateID varchar(20))
insert into @State
select 'CA', 'no1', 'CA' union
select 'GA', 'no2', 'noneCA' union
select 'AR', 'no3', 'noneCA' union
select 'DC', 'no3', 'noneCA' union
select 'MA', 'no3', 'noneCA'


declare @StateID varchar(20), 
	@StateGroup varchar(20),
	@Option int

select 	@StateID = 'CA',
	@StateGroup = 'no2'

set @Option = 2

select State
from @State
where StateID = coalesce(case @Option when 1 then @StateID else null end, StateID) 
	and StateGroup = coalesce(case @Option when 2 then @StateGroup else null end, StateGroup)

Nathan Skerl

cr488
Starting Member

23 Posts

Posted - 2006-04-04 : 14:21:15

Nathan, thank you very much for your help. The last one was very useful.

Thank you all for your help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources