Conditions in a Where Clause? - 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

Transact-SQL (2000)

Conditions in a Where Clause?

Author

Topic

dddtest
Starting Member

2 Posts

Posted - 2005-09-09 : 18:12:22

I ran this by a friend of mine that has some experience in T-SQL. I have limited knowledge. Is it possible to change and add on to arguments in a where clause using some type of condition?

For example:
WHERE
IF @tg >= 3
BEGIN
AND (sd = 1 OR sd = 0)
END

IF @tg < 3
BEGIN
AND wd = 1
END

IF @qw IS NOT NULL
BEGIN
AND re = @re
END

IF @rr IS NULL
BEGIN
AND (rr = 1 OR rt = 0)
END

This returned the syntax errors all pointing to the 'AND' operator. Any suggestions?

Thank you!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-09 : 18:19:35

You just need the right combination of ANDs and ORs. I think this might be what you want:


WHERE 
	(@tg>=3 AND sd IN (0,1) OR
	(@tg<3 AND wd=1) OR
	(@qw IS NOT NULL AND re=@re) OR
	(@rr IS NULL AND (rr=1 OR rt=0))

Tara

dddtest
Starting Member

2 Posts

Posted - 2005-09-09 : 19:05:03

THanks Tara I implemented this method. I didn't get any syntax error but it just seems to return all records in the table. It's almost like the where clause is not even there!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-09 : 19:06:53

It's hard for us to help you unless you show us with data what the problem is. Provide some sample data that represents the data in your table and the expected result set that you want when the query runs on this sample data.

Those outer ORs probably need to be ANDs.

Tara

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-09-09 : 22:59:54

It seems like you need to use dynamic SQL (dynamically build an SQL statement). If your WHERE clause criteria were simpler, you might be able to get away with a series of CASE statements. However, I think you will have to do something like this:


DECLARE @sql varchar(1000) 

SET @sql = 'SELECT YourColumn1, YourColumn2 FROM YourTable WHERE YourFirstCriteria '

IF @tg >= 3
	SET @sql = @sql + 'AND (sd = 1 OR sd = 0) '

IF @tg < 3
	SET @sql = @sql + 'AND wd = 1 '

IF @qw IS NOT NULL
	SET @sql = @sql + 'AND re = ''' + @re + ''' '

IF @rr IS NULL
	SET @sql = @sql + 'AND (rr = 1 OR rt = 0) '

EXEC(@sql)

I'm assuming that @re is of character datatype.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-12 : 01:40:35

explore 'case' used in a where clause...

where case when @tg>=3 then sd=1 or sd=0
when @tg<3 then wd=1
...

you need to type this prioritizing which condition to check first, it works like an if statement

HTH

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources