471,090 Members | 1,318 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

Separate an And/Or parameter

I want a user to be able to search for names in a table of clients
using a single parameter, but be eable to use "and" or "or" IN the
parameter.
Like this:

@NameSearch nvarchar(100)

AS

DECLARE @PartA nvarchar(100), @PartB nvarchar(100)

CASE When @nameSearch Like '%' + ' OR ' + '%'

SELECT @PartA =(how do I get what's to the left of "OR" or "AND")
SELECT @PartB (how do I get what's to the right of "OR" or "AND")

SELECT
c.UniqueID,
c.LastName
FROM
dbo.tblClients c
WHERE
c.LastName = @PartA
OR
c.LastName = @PartB

CASE ELSE

SELECT
c.UniqueID,
c.LastName
FROM
dbo.tblClients c
WHERE
c.LastName = @NameSearch

Jul 23 '05 #1
14 1258

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
I want a user to be able to search for names in a table of clients
using a single parameter, but be eable to use "and" or "or" IN the
parameter.
Like this:

@NameSearch nvarchar(100)

AS

DECLARE @PartA nvarchar(100), @PartB nvarchar(100)

CASE When @nameSearch Like '%' + ' OR ' + '%'

SELECT @PartA =(how do I get what's to the left of "OR" or "AND")
SELECT @PartB (how do I get what's to the right of "OR" or "AND")

SELECT
c.UniqueID,
c.LastName
FROM
dbo.tblClients c
WHERE
c.LastName = @PartA
OR
c.LastName = @PartB

CASE ELSE

SELECT
c.UniqueID,
c.LastName
FROM
dbo.tblClients c
WHERE
c.LastName = @NameSearch


You could probably use a combination of string parsing and dynamic SQL to do
this, but for more complicated cases it would quickly become very difficult
to maintain and secure. Or you could pass in a list of names, and use a
technique like this:
Jul 23 '05 #2
<snip>

You could probably use a combination of string parsing and dynamic SQL to
do this, but for more complicated cases it would quickly become very
difficult to maintain and secure. Or you could pass the names as a list,
and use a technique like this:


Oops - hit Send too quickly...

.... a technique like this:

http://www.sommarskog.se/arrays-in-sql.html

This might also give you some ideas:

http://www.sommarskog.se/dyn-search.html

You might want to consider looking at a reporting tool - if your
requirements become more complex, it will be a lot easier than trying to
write and maintain stored procedures to cover every case that your users
would like to see. MSSQL Reporting Services is free if you have an MSSQL
license, or there are other tools such as Crystal/Business Objects
available.

Simon
Jul 23 '05 #3
laurenq uantrell (la*************@hotmail.com) writes:
I want a user to be able to search for names in a table of clients
using a single parameter, but be eable to use "and" or "or" IN the
parameter.
Like this:

@NameSearch nvarchar(100)

AS

DECLARE @PartA nvarchar(100), @PartB nvarchar(100)

CASE When @nameSearch Like '%' + ' OR ' + '%'

SELECT @PartA =(how do I get what's to the left of "OR" or "AND")
SELECT @PartB (how do I get what's to the right of "OR" or "AND")


I would suggest that it quickly gets messy if you are to parse this in
T-SQL. I don't know the exact context for your problem, but I would
look into parsing these lists in the client, and then feed the stored
procedure somehow. For a more general input, XML could do. Then again,
you will still have to build the SQL dynamically, as every OR or
AND would add one more term to the WHERE clause. If you restrict
the number of OR and AND you support, you can use a couple of
fixed parameters.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
Erland, yes, parsing this in the client seems the best solution. In
this way I can limit the number of "AND" or "OR" links to say, three,
then provide three parameters that can be ignored if blank. Thanks to
you and Simon (Thanks AGAIN, I might add!)
lq

Jul 23 '05 #5
Just thinking further on this, not to get too complicated, how would
one go about creating a stored procedure where the client side looked
like this:

ClientName LIKE "Smith"
AND/OR
City IS "London"
AND/OR
Pet LIKE "Spot"
etc...

Where the user enters the quoted criteria and then selects AND or OR
and LIKE or IS

Jul 23 '05 #6
Here is an old posting of mine that handles building queries with a
canonical disjunctive form representation in a table The origianl
problem was matching people to job requirements.

========
I think what he is after is the ability to load tables with criteria
and not have to use dynamic SQL:

skill = Java AND (skill = Perl OR skill = PHP)

becomes the canonical disjunctive form:

(Java AND Perl) OR (Java AND PHP)

which we load into this table:

CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));

INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');

Assume we have a table of job candidates:

CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));

INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');

The query is simple now:

SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);

You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two. if you can think in sets, you can avoid dynamic SQL and
procedural 99% of the time.

Jul 23 '05 #7
--CELKO-- (jc*******@earthlink.net) writes:
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner


Of course Larry is a winner when it comes to Perl!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8
laurenq uantrell (la*************@hotmail.com) writes:
Just thinking further on this, not to get too complicated, how would
one go about creating a stored procedure where the client side looked
like this:

ClientName LIKE "Smith"
AND/OR
City IS "London"
AND/OR
Pet LIKE "Spot"
etc...

Where the user enters the quoted criteria and then selects AND or OR
and LIKE or IS


I rather not do it at all!

If was meant to be general, I would probably see no way out but to
use dynamic SQL.

From your first example, I got the impression that all search
terms were for the same column, in which case a static solution
would be easier. I don't any examples, though, because I don't
know your business requierments well enough.

In any case, I have a longer article on the topic, that might
give you inspiration: http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9
I think he padded his resume ...Like that Celko giuy claiming 104 years
of SQL experience.

Jul 23 '05 #10
Erland,
That article you linked has been extremely helpful to me over the past
year or so already! Thanks.
lq

Jul 23 '05 #11
I see the wisdom in your solution. In my case however, it's not just
the Skill column that needs to be matched but between one to maybe 20
different columns using search criteria, so the SELECT DISTINCT query
becomes unworkable.
lq

Jul 23 '05 #12
Yeah, but that Celko guy just might have it.

Algol... Simula... Next thing you know, RPG and COBOL!

-PatP

========================================
Please post DDL at the very least, and DML when possible to make it
easier for people to help you.

Please reply only via newsgroups, as I rarely reply to email. The
letters BAM should actually be AOL throughout my whole email address.

*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #13
Erland,
Your Dynamic Search article was extremely helpful and I modified your
Synamix SQL example to include IF statements to create joins in the
SELECT DISTINCT statement:
EXAMPLE:

SELECT @sql =
'INSERT INTO #TEMP(ID, Name, ...)
SELECT DISTINCT
C.ID,
C.Name,...
etc.
FROM
dbo.tblClients C'
IF @par1 IS NOT NULL SELECT @sql = @sql + ' LEFT OUTER JOIN
dbo.tblClientPhones P ON C.ID = P.ID'
IF @par2 IS NOT NULL SELECT @sql = @sql + ' LEFT OUTER JOIN
dbo.tblClientEmail E ON C.ID = E.ID'
.... etc.
SELECT @sql = @sql + ' WHERE

IF @par1 IS NOT NULL SELECT @sql = @sql + ' AND P.PhoneNumber Like
''%'' + @Xpar1 + ''%'''
IF @par2 IS NOT NULL SELECT @sql = @sql + ' AND E.EmailAddress Like
''%'' + @Xpar2 + ''%'''
etc.

Very helful!
This query now has thirty parameters and IF statements to create (or
not) about ten joins and performs incredibly fast on a table with
10,000 records joining many tables that contain about that number each.

Thanks,
lq

Jul 23 '05 #14
laurenq uantrell (la*************@hotmail.com) writes:
This query now has thirty parameters and IF statements to create (or
not) about ten joins and performs incredibly fast on a table with
10,000 records joining many tables that contain about that number each.


Glad to hear that you got it working!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Roberto Becerril | last post: by
28 posts views Thread by kfrost | last post: by
3 posts views Thread by LurfysMa | last post: by
5 posts views Thread by =?iso-8859-1?q?Erik_Wikstr=F6m?= | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.