December 5, 2007 at 4:04 pm
Ok, so I have a procedure which pulls data from several tables, and I want to be able to filter off of several values, but sometimes I won't use some of the filters. here is what I'm trying to do.
ALTER PROCEDURE [dbo].[pr_InventoryList]
@InventoryID
@AcquiredDateLow datetime,
@AcquiredDateHigh datetime,
@ContractDateLow datetime,
@ContractDateHigh datetime,
@status char(1)
SELECT
Table1.column1,
Table2.column1,
Table2.column2,
Table3.column1,
Table4.column1
FROM Table1
LEFT JOIN Table 2 ON Table1.Column9 = Table2.Column11
LEFT JOIN Table 3 ON Table1.Column6 = Table3.Column5
LEFT JOIN Table 4 ON Table1.Column6 = Table4.Column9
WHERE Table1.Column1 = @InventoryID
AND Table2.PurchaseDate BETWEEN @AcquiredDatelow AND @AcquiredDateHigh
AND Table3.ContractDate Between @ContractDateLow AND @ContractDateHigh
AND Table1.Status LIKE @status
My problem is that I'm still learning.:D and I'm not sure how to first off make some of my where clause optional. I always need to used the @InventoryID value and the @status value, but the Date ranges need to be optional....and furthermore... the ContractDate is sometimes a null value if the inventory item was never sold....and I know this statment isn't handling null values on the date columns, but I'm not sure how to do it.
So can anyone help a newb out, or can I phrase my questions better?
December 5, 2007 at 7:04 pm
ALTER PROCEDURE [dbo].[pr_InventoryList]
@InventoryID
@AcquiredDateLow datetime '20071231'
if @AcquiredDateLow = '20071231'
set @AcquiredDateLow = '%'
Guess this is where u stuck.....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 5, 2007 at 11:39 pm
Perfomance-wise it's a bad idea to change the value of parameters in a proc before using them. Confuses the optimiser's row estimates.
Rather define a local variable, set the variable's value depending on th parameter and use the variable in the query
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
December 6, 2007 at 12:18 am
Is this what you're looking for?
SELECTTable1.column1, Table2.column1, Table2.column2, Table3.column1, Table4.column1
FROMTable1
LEFT JOIN Table2 ON Table1.Column9 = Table2.Column11
LEFT JOIN Table3 ON Table1.Column6 = Table3.Column5
LEFT JOIN Table4 ON Table1.Column6 = Table4.Column9
WHERETable1.Column1 = @InventoryID
AND Table1.Status = @status
AND (
( Table2.PurchaseDate BETWEEN @AcquiredDatelow AND @AcquiredDateHigh )
OR ( @AcquiredDatelow IS NULL )
OR ( @AcquiredDateHigh IS NULL )
)
AND (
( Table3.ContractDate BETWEEN @ContractDateLow AND @ContractDateHigh )
OR ( @ContractDateLow IS NULL )
OR ( @ContractDateHigh IS NULL )
OR ( Table3.ContractDate IS NULL )
)
--Ramesh
December 6, 2007 at 12:20 am
Hi,
The best choice can be to use a dynamic sql where you can make the where class optional...
Jai ganesh
December 6, 2007 at 12:24 am
Jai (12/6/2007)
Hi,The best choice can be to use a dynamic sql where you can make the where class optional...
Jai,
I appreciate your feedback but, dynamic SQL is not always the best choice, but the last choice if all else fails!!!!!
--Ramesh
December 6, 2007 at 12:47 am
Ramesh (12/6/2007)
Jai,I appreciate your feedback but, dynamic SQL is not always the best choice, but the last choice if all else fails!!!!!
Performance-wise, with this kind or requirement it sometimes is the best solution. The multiple @variable is null or @variable2 is null or ... tend to confuse the optimiser and result in very poor plans. I've cleaned a couple cases of that out of ofne of my DBs and the performance improvement can be massive, especially if the tables are large
In this case, since its date ranges, I'd probably go for something like this. This is not tested, either for accuracy or performance.
ALTER PROCEDURE [dbo].[pr_InventoryList]
@InventoryID
@AcquiredDateLow datetime = NULL,
@AcquiredDateHigh datetime = NULL,
@ContractDateLow datetime = NULL,
@ContractDateHigh datetime = NULL,
@status char(1)
DECLARE @AcqStart DATETIME, @AcqEnd DATETIME, @ConStart DATE, @ConEnd DATETIME.
-- change the defaults if needed to higher/lower values
SELECT
@AcqStart = CASE WHEN @AcquiredDateLow IS NULL THEN '1900/01/01' ELSE @AcquiredDateLow END,
@AcqEnd = CASE WHEN @AcquiredDateHigh IS NULL THEN '2100/01/01' ELSE @AcquiredDateHigh END,
@ConStart = CASE WHEN @ContractDateLow IS NULL THEN '1900/01/01' ELSE @ContractDateLow END,
@ConEnd = CASE WHEN @ContractDateHigh IS NULL THEN '2100/01/01' ELSE @ContractDateHigh END
SELECT
Table1.column1,
Table2.column1,
Table2.column2,
Table3.column1,
Table4.column1
FROM Table1
LEFT JOIN Table 2 ON Table1.Column9 = Table2.Column11
LEFT JOIN Table 3 ON Table1.Column6 = Table3.Column5
LEFT JOIN Table 4 ON Table1.Column6 = Table4.Column9
WHERE Table1.Column1 = @InventoryID
AND Table2.PurchaseDate BETWEEN @AcqStart AND @AcqEnd
AND (Table3.ContractDate Between @ConStart AND @CoEnd OR Table3.ContractDate IS NULL)
AND Table1.Status LIKE @status
This way, the query won't get the benefit of parameter sniffing on the dates, which is good, cause they can change so much, but does on the inventory and the status and so benefit from (hopefully) better plans.
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
December 6, 2007 at 7:10 am
Thanks for the recommendations, I see my problem with some of my earlier ideas was that I wasn't using brackets correctly.
I like the idea of evaluating the passed in parameter, and if it's null then just using an extreme bottom and top date range value...also the "OR Is Null" within brackets is what I needed. I tried to use it without brackets and well...I got about 10E5 more rows then I wanted hehe.:D
December 6, 2007 at 7:15 am
While it gets messing to maintain, when we have a number of parameters that may or may not be referenced, basically a search screen, this is how we configure the where clause:
SELECT...
FROM...
WHERE...
AND CASE WHEN @Param1 IS NULL THEN 1
WHEN @Param1 = Column1 THEN 1
ELSE 0
END = 1
AND CASE WHEN @Param2 IS NULL THEN 1
WHEN @Param2 = Column2 THEN 1
ELSE 0
END = 1
It works very well and can take advantage of indexes where they are available, unlike so many of the COALESCE and OR solutions that people end up using. I don't take credit for this, it was shown to us by a MS consultant (which one is in debate, I think Bill Sulcius, others think Andy Roberts, I'm giving each named credit).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply