September 2, 2014 at 2:52 am
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
TyreIdCarIdRegionIdCarAgeGroup
NULLNULL1084 2
65351084 1
5351084 1
NULL411085 NULL
120NULLNULL NULL
NULLNULL1084 2
65NULL1084 NULL
September 2, 2014 at 2:57 am
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 ...
September 2, 2014 at 3:03 am
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
)
)
September 2, 2014 at 3:23 am
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
)
September 2, 2014 at 3:35 am
September 2, 2014 at 3:53 am
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 [/url]
The result will be better query performance.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 2, 2014 at 5:59 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2014 at 9:04 am
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
September 2, 2014 at 6:04 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 3, 2014 at 12:37 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply