Erland,
That article you linked has been extremely helpful to me over the past
year or so already! Thanks.
lq

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

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 ***

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

laurenq uantrell (laurenquantrell (AT) hotmail (DOT) com) writes:
Glad to hear that you got it working!


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

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