Separate an And/Or parameter

[laurenq uantrell]

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

[Simon Hayes]

"laurenq uantrell" <la************ *@hotmail.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.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:

[Simon Hayes]

<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

[Erland Sommarskog]

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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[laurenq uantrell]

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

[laurenq uantrell]

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

[--CELKO--]

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_na me
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_na me
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.

[Erland Sommarskog]

--CELKO-- (jc*******@eart hlink.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

I think he padded his resume ...Like that Celko giuy claiming 104 years
of SQL experience.