Dynamic Where Clause Based on Parameter

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

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Where Clause Based on Parameter

Author

Topic

apwestgarth
Starting Member

10 Posts

Posted - 2006-12-12 : 09:58:33

Hello,
I am writing a search facility which requires that the user is able to select multiple parameters for searching with. One of the criterias is the failure time of a test which needs also to allow the user to select the operator against which the failure time is searched against an input value, i.e. whether it is GREATER THAN, LESS THAN or EQUAL TO. I have a stored procedure in place and have attempted to implement this functionality using a CASE statement however I am getting a syntax error on the line which holds the case statement. Could anyone please advise me as to whether I am attempting to do this correctly, is it possible to be done and or is there a better way to implement it? The Code for the SPROC is below.

Thanks

Andrew

CREATE PROCEDURE [dbo].[mpyapftdb_search]
/* Declare Parameters for Searching against */
@FireTestNo bigint,
@Specimen int,
@TestDate datetime,
@FailureTime int,
@FailureTimeOperator nvarchar(2),
@SubstrateType int,
@TestType int,
@GenericType int,
@BatchNumber varchar(50),
@HpA numeric(9),
@DFTFrom numeric(9),
@DFTTo numeric(9),
@SortExpression varchar(8000)
AS
DECLARE @Batch varchar(50)
if(@BatchNumber != NULL)
SET @Batch = '%' + @BatchNumber + '%'

DECLARE @FailureOperator varchar(50)
if(@FailureTimeOperator = 'gt')
SET @FailureOperator = '>'

if(@FailureTimeOperator = 'lt')
SET @FailureOperator = '<'

if(@FailureTimeOperator = 'eq')
SET @FailureOperator = '='

if(@FailureTimeOperator = null)
SET @FailureOperator = 'LIKE'

SELECT Spec.FireTestNo,
Spec.Specimen,
Spec.SectionReference,
gt.GenericTypeDesc,
spec.AVEDFT,
spec.BatchA,
spec.BatchB,
st.SubstrateTypeLength + ' ' + st.SubstrateTypeDesc + ' Batch: ' + @Batch AS Substrate,
spec.HpA,
tt.TestTypeDesc,
fd.AVE150,
fd.AVE400,
fd.AVE500,
fd.AVE550,
fd.AVE620

FROM Specimens spec INNER JOIN
TestTypes tt ON spec.TestTypeID = tt.TestTypeID INNER JOIN
GenericTypes gt ON spec.fGenericType = gt.GenericTypeID INNER JOIN
ReinforcementTypes rt ON spec.ReinforcementID = rt.ReinforcementTypeID INNER JOIN
SubstrateTypes st ON spec.SubstrateType = st.SubstrateTypeID INNER JOIN
FurnaceData fd ON spec.FurnaceDataID = fd.FurnaceDataID

WHERE Spec.FireTestNo LIKE COALESCE(@FireTestNo, spec.FireTestNo)
AND spec.Specimen LIKE COALESCE(@Specimen, spec.Specimen)
AND spec.TestDate LIKE COALESCE(@TestDate, spec.TestDate)
AND spec.SubstrateType LIKE COALESCE(@SubstrateType, spec.SubstrateType)
AND spec.TestTypeID LIKE COALESCE(@TestType, spec.TestTypeID)
AND spec.fGenericType LIKE COALESCE(@GenericType, spec.fGenericType)
AND (spec.BatchA LIKE ISNULL(@Batch, spec.BatchA)
OR spec.BatchB LIKE ISNULL(@Batch, spec.BatchB))
AND spec.HpA LIKE COALESCE(@Hpa, spec.HpA)
ANDspec.AveDFT BETWEEN COALESCE(@DFTFrom, spec.AveDFT)
AND COALESCE(@DFTTo, spec.AveDFT)
Select (@FailureTimeOperator) CASE
WHEN @FailureTimeOperator = 'gt' THEN
AND (fd.AVE150 > COALESCE(@FailureTime, fd.AVE150)
AND (fd.AVE400 > COALESCE(@FailureTime, fd.AVE400)
AND (fd.AVE500 > COALESCE(@FailureTime, fd.AVE500)
AND (fd.AVE550 > COALESCE(@FailureTime, fd.AVE550)
AND (fd.AVE620 > COALESCE(@FailureTime, fd.AVE620))
WHEN @FailureTimeOperator = 'lt' THEN
AND (fd.AVE150 < COALESCE(@FailureTime, fd.AVE150)
AND (fd.AVE400 < COALESCE(@FailureTime, fd.AVE400)
AND (fd.AVE500 < COALESCE(@FailureTime, fd.AVE500)
AND (fd.AVE550 < COALESCE(@FailureTime, fd.AVE550)
AND (fd.AVE620 < COALESCE(@FailureTime, fd.AVE620))
WHEN @FailureTimeOperator = 'eq' THEN
AND (fd.AVE150 = COALESCE(@FailureTime, fd.AVE150)
AND (fd.AVE400 = COALESCE(@FailureTime, fd.AVE400)
AND (fd.AVE500 = COALESCE(@FailureTime, fd.AVE500)
AND (fd.AVE550 = COALESCE(@FailureTime, fd.AVE550)
AND (fd.AVE620 = COALESCE(@FailureTime, fd.AVE620))
ELSE
AND (fd.AVE150 LIKE COALESCE(@FailureTime, fd.AVE150)
AND (fd.AVE400 LIKE COALESCE(@FailureTime, fd.AVE400)
AND (fd.AVE500 LIKE COALESCE(@FailureTime, fd.AVE500)
AND (fd.AVE550 LIKE COALESCE(@FailureTime, fd.AVE550)
AND (fd.AVE620 LIKE COALESCE(@FailureTime, fd.AVE620))
END
ORDER BY spec.SectionReference

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-12 : 11:18:12

This may help you

http://www.sqlteam.com/item.asp?ItemID=2077

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-12-12 : 14:55:27

"WHERE Spec.FireTestNo LIKE COALESCE(@FireTestNo, spec.FireTestNo)"

Two problems that I encounter, in the real world, with this:

1) If @FireTestNo IS NULL then it induces a Table Scan

2) It bypasses any index on Spec.FireTestNo

Mind you, this is never going to be brilliantly-efficient (although I do believe there are ways of achieving that - IF it is critical that performance is maintained on a sufficiently-large table.

I am inclined to think that this is worth considering:


WHERE (@FireTestNo IS NULL OR Spec.FireTestNo = spec.FireTestNo)
      OR ( ... next test ... )

on the grounds that NULL entries would skip the test, and prevent a SCAN [or utilise an index lookup]

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources