Boolean Logic within SQL - 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

Boolean Logic within SQL

Author

Topic

Siopold
Starting Member

9 Posts

Posted - 2009-06-08 : 17:11:58

Hi,

I would like to apply some logic within an SQL statement. I have read abit about using boolean logic instead of the CASE clause. My problem is as follows;

I am selecting from a table and I want to filter using a where based upon an input parameter passed into the SQL statement, so;

Select field1, field2, field3, field4, field5 from tablea
where :inputParm1 <> ' ' and :inputName = field1

The problem I have is that I want to pass in 5 parameters where I may use the 5 parameters or 4 or 3 etc. so;

If I pass in the following combination
So I want to see all the 'Smith's' in 'London'

inputParm1 = 'Smith'
inputParm2 = ''
inputParm3 = ''
inputParm4 = 'London'
inputParm5 = ''

Select field1, field2, field3, field4, field5 from tablea
where :inputParm1 <> ' ' then :inputParm1 = field1
and :inputParm2 <> ' ' then :inputParm2 = field2
and :inputParm3 <> ' ' then :inputParm3 = field3
and :inputParm4 <> ' ' then :inputparm4 = field4
and :inputParm5 <> ' ' then :inputParm5 = field5

I want to do something like the above in SQL but I don't think I can do it with CASE as I don't want an either/or check I want to include all checks where the input parameters are not blank so that I can dynamically filter based upon input parameters.

Any help appreciated. I have sorted out the problem by using a combination of SQL and a programming language which is fine - essentially it is a series of IFs done after the Select rejecting records that I don't want so;

If inputParm <> *blanks and inputParm <> field1
then iterate back to top of loop (i.e. do not process this record)
End

I am curious as to how you achieve this in SQL. All help appreciated. I hope I have described the issue clearly, if not I can update.

Regards

D

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-08 : 18:05:33

What you are talking about is called Optional Parameters. Here is one of many links that describe how to do this in SQL: http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-09 : 02:41:14

Also read
www.sommarksog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

prav3575
Starting Member

5 Posts

Posted - 2009-06-09 : 07:55:42

this is just very simple dynamic query generation using stored procedure

create procedure chk_proc ( @p1 as varchar(10),@p2 as varchar(10), @p3 as varchar(10), @p4 as varchar(10))
as
begin
declare
@sql varchar(100)
begin

select @sql='select * from table_A where '

if @p1<>''
select @sql=@sql+ 'col1='+@p1+' and '

if @p2<>''
select @sql=@sql+ 'col2='+@p2+' and '

if @p3<>''
select @sql=@sql+ 'col3='+@p3+ ' and '

if @p4<>''
select @sql=@sql+ 'col4='+@p4+' and '

set @sql = left(@sql,len(@sql)-3)
select @sql

end
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-09 : 08:20:09

quote:
Originally posted by prav3575

this is just very simple dynamic query generation using stored procedure

create procedure chk_proc ( @p1 as varchar(10),@p2 as varchar(10), @p3 as varchar(10), @p4 as varchar(10))
as
begin
declare
@sql varchar(100)
begin

select @sql='select * from table_A where '

if @p1<>''
select @sql=@sql+ 'col1='+@p1+' and '

if @p2<>''
select @sql=@sql+ 'col2='+@p2+' and '

if @p3<>''
select @sql=@sql+ 'col3='+@p3+ ' and '

if @p4<>''
select @sql=@sql+ 'col4='+@p4+' and '

set @sql = left(@sql,len(@sql)-3)
select @sql

end
end

Make sure you read the link I posted

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-09 : 08:45:48

quote:
Originally posted by madhivanan

Also read
www.sommarksog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

This is broken (transposed k).

is it this?
http://www.sommarskog.se/dyn-search.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-09 : 09:09:59

quote:
Originally posted by Transact Charlie

quote:
Originally posted by madhivanan

Also read
www.sommarksog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

This is broken (transposed k).

is it this?
http://www.sommarskog.se/dyn-search.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Yes it is. Thanks

Madhivanan

Failing to plan is Planning to fail

Siopold
Starting Member

9 Posts

Posted - 2009-06-09 : 16:13:29

Thanks guys will have a look at those links, thanks for the help

Regards

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources