Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT Multiple criterias if not null Expand / Collapse
Author
Message
Posted Tuesday, September 2, 2014 2:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:33 AM
Points: 6, Visits: 17
HI all,

I have a dataset where i want to select the records that matches my input values. But i only want to try macthing a field in my dataset aginst the input value, if the dataset value is not NULL.

I allways submit all 4 input values.
@Tyreid, @CarId,@RegionId,@CarAgeGroup


So for the first record in the dataset i get a succesfull output if my input values matches RegionId and CarAgeGroup.

I cant figure out how to create the SQl script for this SELECT?

My dataset
TyreId CarId RegionId CarAgeGroup
NULL NULL 1084 2
65 35 1084 1
5 35 1084 1
NULL 41 1085 NULL
120 NULL NULL NULL
NULL NULL 1084 2
65 NULL 1084 NULL
Post #1609499
Posted Tuesday, September 2, 2014 2:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 5,488, Visits: 10,339
Comparing any value to NULL will return False. Please will you show us your query?

John

Edit - I think I see what you're trying to do. Will this work?

WHERE COALESCE(TyreID, @TyreID) = @TyreID
AND COALESCE(CarID, @CarID) = @CarID
AND ...

Post #1609500
Posted Tuesday, September 2, 2014 3:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:33 AM
Points: 6, Visits: 17
Hi John,

So far i have this.


DECLARE @CarName nvarchar(max)
DECLARE @CarAge int
DECLARE @TyreId int
DEclare @TyreType int
DECLARE @Zipcode int

SET @TyreId = 65
SET @CarName = 'CITROEN'
SET @CarAge = 2002
SET @Zipcode = 2770

SELECT
[TyreId]
,[CarId]
,[RegionsNr]
,[CarAgeGroup]
FROM [dbo].[Partner_Campaigns]

WHERE

(
TyreId = (
CASE
WHEN NOT(TyreId) IS NULL THEN @TyreId
ELSE NULL
END
)
OR
CarId = (
CASE
WHEN NOT(CarId) IS NULL THEN (SELECT id FROM Partner_CarModels WHERE Make like '%' + @CarName + '%')
ELSE NULL
END
)
OR
RegionsNr = (
CASE
WHEN NOT(RegionsNr) IS NULL THEN (SELECT DISTINCT(Regionsnr) FROM Partner_Regioner_Kommuner_Postnr WHERE Postnr=@Zipcode)
ELSE NULL
END
)
OR
CarAgeGroup = (
CASE
WHEN NOT(CarAgeGroup) IS NULL THEN
isnull((
CASE
WHEN (YEAR(GETDATE()) - CAST(@CarAge AS Int)) < 6 THEN 1
WHEN (YEAR(GETDATE()) - CAST(@CarAge AS Int)) > 5 THEN 2
ELSE 0
END
),
NULL
)
ELSE NULL
END
)
)
Post #1609503
Posted Tuesday, September 2, 2014 3:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:33 AM
Points: 6, Visits: 17
Hi John,

Yes COALESCE is the way.

Thanks a lot!!


Changed to this and it works perfect.


DECLARE @CarName nvarchar(max)
DECLARE @CarAge int
DECLARE @TyreId int
DEclare @TyreType int
DECLARE @Zipcode int
DECLARE @CarId int
DECLARE @RegionId int

SET @TyreId = 65
SET @CarName = 'CITROEN'
SET @CarAge = 2002
SET @Zipcode = 2770


SELECT @CarId = (SELECT id FROM Partner_CarModels WHERE Make like '%' + @CarName + '%')
SELECT @RegionId = (SELECT DISTINCT(Regionsnr) FROM Partner_Regioner_Kommuner_Postnr WHERE Postnr=@Zipcode)

SELECT
campaignname,
[TyreId]
,[CarId]
,[RegionsNr]
,[CarAgeGroup]
FROM [dbo].[Partner_Campaigns]

WHERE

COALESCE(TyreId, @TyreId) = @TyreId
AND
COALESCE(CarId, @CarId) = @CarId
AND
COALESCE(RegionsNr, @RegionId) = @RegionId
AND
COALESCE(CarAgeGroup,
(
CASE
WHEN (YEAR(GETDATE()) - CAST(@CarAge AS Int)) < 6 THEN 1
WHEN (YEAR(GETDATE()) - CAST(@CarAge AS Int)) > 5 THEN 2
END
)

) = (
CASE
WHEN (YEAR(GETDATE()) - CAST(@CarAge AS Int)) < 6 THEN 1
WHEN (YEAR(GETDATE()) - CAST(@CarAge AS Int)) > 5 THEN 2
END
)
Post #1609512
Posted Tuesday, September 2, 2014 3:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 5,488, Visits: 10,339
Happy to help. You can make it a bit neater by putting the CASE logic near the top:

...
SET @CarAge = CASE
-- don't need to cast as int because that's the data type of the variable
WHEN YEAR(GETDATE() - @CarAge) < 6 THEN 1
ELSE 2
END
...
AND COALESCE(CarAgeGroup, @CarAge) = CarAgeGroup
END

John
Post #1609514
Posted Tuesday, September 2, 2014 3:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 PM
Points: 3,438, Visits: 5,390
I think you should re-think your approach and write it as a dynamic search SP:

How to Design, Build and Test a Dynamic Search Stored Procedure

The result will be better query performance.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1609522
Posted Tuesday, September 2, 2014 5:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
peter larsen-490879 (9/2/2014)
Hi John,

Yes COALESCE is the way.


It works, providing good performance is not a requirement. If performance is a requirement, see http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1609574
Posted Tuesday, September 2, 2014 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 5,488, Visits: 10,339
Gail, this isn't a catch-all query. According your article, a catch-all query is one "where the user may enter any one (or more) of a number of optional parameters". Here, the clearly stated requirement is different: "I allways submit all 4 input values". Those four values must, therefore, always be tested for - I don't see any other way to fulfil the requirement than the basic structure the OP ended up with in his final post.

John
Post #1609663
Posted Tuesday, September 2, 2014 6:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 PM
Points: 3,438, Visits: 5,390
John Mitchell-245523 (9/2/2014)
Gail, this isn't a catch-all query. According your article, a catch-all query is one "where the user may enter any one (or more) of a number of optional parameters". Here, the clearly stated requirement is different: "I allways submit all 4 input values". Those four values must, therefore, always be tested for - I don't see any other way to fulfil the requirement than the basic structure the OP ended up with in his final post.

John


I've written quite a few dynamic search stored procedures, with my thanks out to Gail and her SQL-in-the-Wild article for showing me the way.

I always allow for the case of submitting all parameters. It is just that oftentimes, some (or many) of them are NULL. And it appears that this could be the case for this OP, given his attempts to handle NULLs in the example he provided.

This is a case where I at least (and probably Gail too) are attempting to shift the OP's paradigm.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1609853
Posted Wednesday, September 3, 2014 12:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:33 AM
Points: 6, Visits: 17
Hi,

Just read your posts.

It could also work, the way you describe, where i only submit either of the 4 values or all 4, in some cases.

Didn´t know,that my statement 'I always submit all 4 values', had such a big impact in the way, the SP is constructed. Actually I thought everything would easier, if all 4 values were always submitted.

Sorry for not being clear.
Post #1609883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse