Return rows by AND/OR

Question

Post reply

Return rows by AND/OR

David Diener

SSC-Addicted

Points: 410
More actions
September 1, 2007 at 5:54 pm

#180621

OK, I've used a solution using ColdFustion that resolves my problem and I want to convert ColdFusion to T-SQL.
problem:
return recordset by and/or predicate(I wish)
example:
Let's say I want to return rows from pubs with 3 input parameters
@aufname
@aulname
@austate
The result set should return rows with specific records for:
and/or marjorie, and/or green, and/or ca
I am getting all records that include the string values marjore and green and ca
Same with marjorie, ca
Specific records are returned OK with only 1 input parameter specified such as @aulname = 'Green'
Can anyone give me an example of how to do this with t-sql,preferable not using dynamic sql?
Perhaps an array or a case example?
Many thanks in advance.
--------------------------------------------------------------------------------------------------------
USE [pubs]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pubsSearch2]
@aufname VARCHAR(20) = NULL,
@aulname VARCHAR(20) = NULL,
@austate VARCHAR(20) = NULL
AS
SELECT
au_id, au_lname, au_fname, phone, address, city, state, zip, contract, au_date
FROM authors
WHERE
(au_id = '%') OR
(au_lname LIKE @aulname + '%') OR
(au_fname LIKE @aufname + '%') OR
(state LIKE @austate + '%')
--EXEC dbo.usp_pubsSearch2 @aufname = 'Marjorie', @aulname = 'Green', @austate = 'CA'
--EXEC dbo.usp_pubsSearch2 @aulname = 'Green'
--EXEC usp_pubsSearch2 @aufname = 'Marjorie' ,@austate = 'CA'
--EXEC usp_pubsSearch2 @aufname = 'Marjorie'
----------------------------------------------------------------------------------------------------------
Kindest Regards,
(Anonymous) SQL Server DBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

SELECT

au_id,

au_lname

,

au_fname

,

phone

,

address,

city

,

state,

zip

,

contract,

au_date

FROM

authors

WHERE

(@aulname IS NULL OR au_lname LIKE @aulname + '%')

AND (@aufname IS NULL OR au_fname LIKE @aufname + '%')

AND (@austate IS NULL OR state LIKE @austate + '%')

N 56°04'39.16"
E 12°55'05.25"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Just be aware that that kind of catch-all query is extremely prone to poor execution plans, due to parameter sniffing with varying parameters.

Also, the query optimiser tends to mis-read the multiple @variable IS NULL lines, assuming that all are true or all are false (That comment came from one of the SQL PSS Engineers at the Pass conference last year)

If you absolutely have to use that form of query, make sure that it recompiles on every execution (OPTION (RECOMPILE) at the end of the query) or you risk very erratic query performance.

Been there, done that, spend most of the night debugging.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Diener SSC-Addicted Points: 410 More actions · Answer 3

That did the trick.

Thank you all so much.

I appreciate the note that the query optimizer tends to mis-read the multiple @variable IS NULL lines.

Kindest Regards,

(Anonymous) SQL Server DBA