March 13, 2018 at 10:25 am
I have a stored procedure that has a parameter of date named dateStored.
The default value is null.
In the stored procedure I have a merge statement that the select part has a filter like this:
where term=2018 and storedDate>@dateStored.
I suppose if a null date passed it will run for the records of all date stored, because storedDate>null basically means nothing, we can ignore that filter, is that correct?
Thanks
March 13, 2018 at 10:43 am
sqlfriends - Tuesday, March 13, 2018 10:25 AMI have a stored procedure that has a parameter of date named dateStored.The default value is null.
In the stored procedure I have a merge statement that the select part has a filter like this:
where term=2018 and storedDate>@dateStored.
I suppose if a null date passed it will run for the records of all date stored, because storedDate>null basically means nothing, we can ignore that filter, is that correct?
Thanks
Not quite. If you changed the filter to use ISNULL(@dateStored, '19000101'), you would get every date greater than January 1st, 1900, whenever your parameter is NULL. However, if you just let it be null, that condition will never be satisfied, because nothing can be <, >, or = to NULL. Null values CAN NOT be compared to successfully. All such comparisons will result in a false boolean value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 13, 2018 at 10:46 am
sqlfriends - Tuesday, March 13, 2018 10:25 AMI have a stored procedure that has a parameter of date named dateStored.The default value is null.
In the stored procedure I have a merge statement that the select part has a filter like this:
where term=2018 and storedDate>@dateStored.
I suppose if a null date passed it will run for the records of all date stored, because storedDate>null basically means nothing, we can ignore that filter, is that correct?
Thanks
Nope. storedDate > null is not true. You would want something like this: storedDate > isnull(@dateStored,'1900-01-01') This of course assumes that all your dates will be greater than 1900-01-01.
March 13, 2018 at 11:05 am
Thanks,
oh it returns not true, then 0 record. Thanks,
So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)
Correct?
March 13, 2018 at 11:08 am
sqlfriends - Tuesday, March 13, 2018 11:05 AMThanks,
oh it returns not true, then 0 record. Thanks,So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)Correct?
I would use the constant value in the ISNULL.
March 13, 2018 at 11:10 am
Is that because better performance? Thanks
March 13, 2018 at 11:17 am
sqlfriends - Tuesday, March 13, 2018 11:10 AMIs that because better performance? Thanks
Test it, just be sure to use a large dataset, like a million rows or more if possible.
March 13, 2018 at 11:20 am
Ok, thanks
March 13, 2018 at 11:26 am
Lynn Pettis - Tuesday, March 13, 2018 11:08 AMsqlfriends - Tuesday, March 13, 2018 11:05 AMThanks,
oh it returns not true, then 0 record. Thanks,So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)Correct?
I would use the constant value in the ISNULL.
Depending on how the paramater is used in the rest of the procedure, it may just be simpler to replace the default value of the parameter with the constant.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 13, 2018 at 11:30 am
sqlfriends - Tuesday, March 13, 2018 11:05 AMThanks,
oh it returns not true, then 0 record. Thanks,So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')
Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 13, 2018 at 11:40 am
ScottPletcher - Tuesday, March 13, 2018 11:30 AMsqlfriends - Tuesday, March 13, 2018 11:05 AMThanks,
oh it returns not true, then 0 record. Thanks,So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 13, 2018 at 11:49 am
sgmunson - Tuesday, March 13, 2018 11:40 AMScottPletcher - Tuesday, March 13, 2018 11:30 AMsqlfriends - Tuesday, March 13, 2018 11:05 AMThanks,
oh it returns not true, then 0 record. Thanks,So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
"A comparison to NULL always returns false." Uh, no, the whole point of my post is that it does NOT return "false" (or "not true", as the OP put it). If it did return "false", then NOT (<NULL comparison>) would, by definition, return "true", but of course it does not.
Btw, I very clearly stated "a comparison to NULL" myself, as any function that's designed to deal with NULLs may be able to yield a true or false result. But a comparison to NULL does not and cannot, and I think that's an important concept for posters here to understand,even though it can be a painful step for them. Frankly NULLs are a royal pain to deal with, but better to have some mechanism for dealing with missing values other than old-time "magic" values, which were even worse.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 13, 2018 at 11:52 am
ScottPletcher - Tuesday, March 13, 2018 11:30 AMsqlfriends - Tuesday, March 13, 2018 11:05 AMThanks,
oh it returns not true, then 0 record. Thanks,So below either one should work,
where sg.storedDate > =isnull(@dateStored,'1900-01-01')
or
Where sg.storeDate>=isnull(@dateStored, sg.storeDate)Correct?
Never use a function against a table column if at all possible. Thus, you should always use the first style, or even better:
WHERE (sg.storedDate IS NULL OR sg.storedDate >= '19000101')Btw, a comparison to NULL does NOT return "not true". It returns NULL, roughly "unknown". That is, if you code:
WHERE sg.storedDate >= @dateStored
When @dateStored is NULL, the result is NULL, which is not the same as "not true". If it were, then:
WHERE NOT (sg.storedDate >= @dateStored)
would return "true" (not "not true" being true) but it does not, because "not true" is never the result.
Actually your IS NULL OR predicate doesn't even match the code provided by the OP. Using this format the code would look like:
WHERE (@dateStored IS NULL OR sg.storedDate >= @dateStored)
Sorry if I over simplified my response, but his query would still return 0 rows as written if @dateStored was null.
March 13, 2018 at 11:56 am
Agree on that.
This is not a query, it is a stored procedure that has a date parameter that has a default value null
March 13, 2018 at 12:00 pm
sqlfriends - Tuesday, March 13, 2018 11:56 AMAgree on that.
This is not a query, it is a stored procedure that has a date parameter that has a default value null
Might want to change the default value to a hardcoded value that precedes any date in your systems.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply