June 3, 2009 at 10:03 am
Hi,
I have a table as so....
Customer Job
-------- ---
1 2
2 6
3 3
Null 4
Null 5
I have a procedure that is used in a Crystal Report as so.....
declare @BeginCustomer int = 1
declare @EndCustomer int = 6
select * from myTable
where Customer >= @BeginCustomer and <= @EndCustomer The report is set up this way so the end user can select a range of Customers for the report, i.e. 1-2 and get just Customer 1 and 2. But if they leave blank it should return all records, even where Customer is Null. Any ideas or advice on how to write? Thanks, Mike
June 3, 2009 at 10:53 am
declare @T1 table
(Customer int,
Job int)
INSERT INTO @T1 (Customer, Job) VALUES (1, 22)
INSERT INTO @T1 (Customer, Job) VALUES (2, 23)
INSERT INTO @T1 (Customer, Job) VALUES (3, 24)
INSERT INTO @T1 (Customer, Job) VALUES (4, 25)
INSERT INTO @T1 (Customer, Job) VALUES (Null, 26)
INSERT INTO @T1 (Customer, Job) VALUES (Null, 27)
INSERT INTO @T1 (Customer, Job) VALUES (Null, 28)
select * from @T1
declare @BeginCustomer int
set @BeginCustomer = 1 --Default value
declare @EndCustomer int
set @EndCustomer = 10 --Default value
select * from @T1
where (Customer >= @BeginCustomer OR @BeginCustomer is null)
and (Customer = @BeginCustomer OR @BeginCustomer is null)
and (Customer <= @EndCustomer OR @EndCustomer is null)
--works
June 3, 2009 at 11:24 am
It's the way nulls are evaluated. They don't evaluate to true or false, they evaluate to unknown.
So the statement 1=1 is "true" and null = null is "unknown". Likewise, the statement 10>1 is "true" and 10 > null is "unknown". Neither true nor false.
Therefore a statement of Customer > null evaluates to "unknown" not "true" and not "false".
There are many different ways to handle nulls. The usual option, don't insert null values to this column. If null values need to be included in result sets with certain criteria, give them the appropriate value to be included, either through a default constraint, trigger, during insert etc etc.
If giving a default value isn't feasible and the null values have to be dealt with at run time, there's a decision that has to be made. "Does null have a value, does it mean something?"
For this we'll assume that null means exactly what it is, "Unknown". Next we'll assume that anytime the customer is "Unknown" we want to see it (just in case).
DECLARE @BeginCustomer int = 1
DECLARE @EndCustomer int = 6
SELECT * FROM myTable
WHERE Customer >= @BeginCustomer and <= @EndCustomer
OR Customer is null
What this accomplishes is that if our range parameters are 2 and 3, the results will contain customers 2, 3 and null. The nulls are included because we're treating null as "Unknown", we don't know if null is 2 or 3 or 10, so just incase we want to see it.
Now, a diffent assumption about null is that still means "unknown", but we only want to see it when the range isn't supplied because that's the only time the procedure doesn't know what we're looking for.
So, here's how to achieve that
DECLARE @BeginCustomer INT
DECLARE @EndCustomer INT
-- Notice there are no defaults here
-- Assume these are set in a SP parameter, if not a set statment and logic need to be built
IF @BeginCustomer is null or @EndCustomer is null
-- No range was selected assume everything is wanted
SELECT * FROM myTable
-- If you range of 1-6 is required the following where clause will acheive that
-- WHERE Customer BETWEEN 1 AND 6
-- OR Customer is null
ELSE
-- A range was specified, assume specific values are wanted
SELECT * FROM myTable
WHERE Customer BETWEEN @BeginCustomer AND @EndCustomer
I know this is long winded, but understanding nulls and how SQL interprets them is very important when dealing with queries and I wanted to take the time to expand on the concept here.
Hope this helps.
-
June 3, 2009 at 6:55 pm
computer.mike (6/3/2009)
where (Customer >= @BeginCustomer OR @BeginCustomer is null)
and (Customer = @BeginCustomer and Customer <= @EndCustomer
GO
[/code]
In this way, each separate procedure has an optimum execution plan that isn't prone to parameter sniffing.
Also, notice that I changed the table variable to a temporary table--- table parameters are only in scope in the procedure in which it was defined.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply