And/Or Function - 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

Development Tools

ASP.NET

And/Or Function

Author

Topic

Iain Duthie
Starting Member

17 Posts

Posted - 2005-02-17 : 11:18:57

I was wondering if it was possible to have an and/or function in a Sql statements.

Cheers
Iain.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-17 : 11:45:15

There are people on this forum that can mow your lawn with sql statments. You just have to be a little more specific with your question ;)

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 12:02:51

My ESP server tells me you want this:
http://weblogs.sqlteam.com/jeffs/archive/2004/07/11/1744.aspx

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

Iain Duthie
Starting Member

17 Posts

Posted - 2005-02-18 : 09:43:27

Well I have to be able to search a table for data but I may only know certain parts of the data such us the author, the title or its location. I want to be able to say select author and/or title and/or location. The user may know a few details or only know one detail.

Cheers
Iain

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 10:38:05

I think there has been some discusions on this topic here on the forums,
but I have no links at the moment.
I think there are a number of ways to do this, with pros/cons, index usage etc.

One basic problem is if you want the user to be able to explicitely search for null columns.
(avoid nulls in the database, btw)

Here is one basic way:

create sproc_dynamicparam
	@a <datatype> = null
	,@b <datatype> = null
as
set nocount on

select
	a,b
from
	tbl
where
	(a = @a or @a is null)
	and
	(b = @b or @b is null)

alternative:


...
where
	coalesce(@a,a) = a
	and
	coalesce(@b,b) = b

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources