December 18, 2013 at 12:15 pm
Can someone tell me what the following statement is doing??
IF ISNULL(@category, '') = ''
I understand it's testing whether the variable @category is null, but what I don't understand is what the values in the parentheses mean. (@category,'').... what is that?!?!
Thanks in advance.
Carol
December 18, 2013 at 12:29 pm
Have you read the following?
http://msdn.microsoft.com/en-us/library/ms184325(v=sql.105).aspx
In your case above if @category is null it is replaced with an empty string.
December 18, 2013 at 12:34 pm
OMG!!!!! Thank you SO much for sharing that!! I know just enough SQL to be dangerous.... hopefully this will make me a bit less so!!! 😀
December 18, 2013 at 3:48 pm
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
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".
December 18, 2013 at 4:00 pm
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
Shouldn't it be better to remember not to use functions on a column in a WHERE clause, and keep using functions when their functionality is exactly what we need?
December 18, 2013 at 4:36 pm
Luis Cazares (12/18/2013)
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
Shouldn't it be better to remember not to use functions on a column in a WHERE clause, and keep using functions when their functionality is exactly what we need?
Considering 1) this isn't a WHERE clause and 2) it is being used on a variable which means the function will be evaluated once even if this had been a WHERE clasue like this: WHERE isnull(@category,'') = ''.
December 18, 2013 at 5:12 pm
Luis Cazares (12/18/2013)
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
Shouldn't it be better to remember not to use functions on a column in a WHERE clause, and keep using functions when their functionality is exactly what we need?
ISNULL() is so inherently less clear that it generated this q! So it's certainly not "needed", "exactly" or otherwise.
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".
December 18, 2013 at 5:18 pm
Lynn Pettis (12/18/2013)
Luis Cazares (12/18/2013)
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
Shouldn't it be better to remember not to use functions on a column in a WHERE clause, and keep using functions when their functionality is exactly what we need?
Considering 1) this isn't a WHERE clause and 2) it is being used on a variable which means the function will be evaluated once even if this had been a WHERE clasue like this: WHERE isnull(@category,'') = ''.
Sure, until/unless someone changes the category to come from a joined (lookup?) table, and quickly changes the code in the WHERE clause to:
isnull(category,'') = ''
which might then prevent index seeks.
Given that ISNULL is less clear anyway, why bother with it at all!?
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".
December 18, 2013 at 5:20 pm
Lynn Pettis (12/18/2013)
Luis Cazares (12/18/2013)
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
Shouldn't it be better to remember not to use functions on a column in a WHERE clause, and keep using functions when their functionality is exactly what we need?
Considering 1) this isn't a WHERE clause and 2) it is being used on a variable which means the function will be evaluated once even if this had been a WHERE clasue like this: WHERE isnull(@category,'') = ''.
Only unclear until one takes the time to read BOL to see what it is doing. Same with any function in any programming language.
Basic rule, RTM (Read The Manual).
December 18, 2013 at 5:24 pm
Lynn Pettis (12/18/2013)
Lynn Pettis (12/18/2013)
Luis Cazares (12/18/2013)
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
Shouldn't it be better to remember not to use functions on a column in a WHERE clause, and keep using functions when their functionality is exactly what we need?
Considering 1) this isn't a WHERE clause and 2) it is being used on a variable which means the function will be evaluated once even if this had been a WHERE clasue like this: WHERE isnull(@category,'') = ''.
Only unclear until one takes the time to read BOL to see what it is doing. Same with any function in any programming language.
Basic rule, RTM (Read The Manual).
Obscurity for the sake of "proving" one "knows more" -- no thanks.
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".
December 18, 2013 at 5:28 pm
ScottPletcher (12/18/2013)
Lynn Pettis (12/18/2013)
Lynn Pettis (12/18/2013)
Luis Cazares (12/18/2013)
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
Shouldn't it be better to remember not to use functions on a column in a WHERE clause, and keep using functions when their functionality is exactly what we need?
Considering 1) this isn't a WHERE clause and 2) it is being used on a variable which means the function will be evaluated once even if this had been a WHERE clasue like this: WHERE isnull(@category,'') = ''.
Only unclear until one takes the time to read BOL to see what it is doing. Same with any function in any programming language.
Basic rule, RTM (Read The Manual).
Obscurity for the sake of "proving" one "knows more" -- no thanks.
Not proving anyone knows more through obscurity. Made a simple observation, know what a function does by reading the manual, regardless of the language. And sorry, this is one that everyone should know because it can be used where IS NULL can't.
December 18, 2013 at 5:31 pm
Lynn Pettis (12/18/2013)
ScottPletcher (12/18/2013)
Lynn Pettis (12/18/2013)
Lynn Pettis (12/18/2013)
Luis Cazares (12/18/2013)
ScottPletcher (12/18/2013)
Better to avoid ISNULL, since you should never use it in a WHERE clause. I also think that exactly what's being tested is clearer.
IF (@category IS NULL OR @category = '')
Shouldn't it be better to remember not to use functions on a column in a WHERE clause, and keep using functions when their functionality is exactly what we need?
Considering 1) this isn't a WHERE clause and 2) it is being used on a variable which means the function will be evaluated once even if this had been a WHERE clasue like this: WHERE isnull(@category,'') = ''.
Only unclear until one takes the time to read BOL to see what it is doing. Same with any function in any programming language.
Basic rule, RTM (Read The Manual).
Obscurity for the sake of "proving" one "knows more" -- no thanks.
Not proving anyone knows more through obscurity. Made a simple observation, know what a function does by reading the manual, regardless of the language. And sorry, this is one that everyone should know because it can be used where IS NULL can't.
Huh? Where can't "IS NULL" be used in place of ISNULL()? At any rate, the ANSI-standard COALESCE() could always be used in place of ISNULL().
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".
December 18, 2013 at 5:33 pm
Scott,
I'm not sure how that's more or less obscure, but it's certainly easier for me to read. I work with a lot of 'iffy' data in loaders, and a structure like this is pretty common for me:
WHERE
ISNULL( LTRIM(RTRIM(a.field)), '') <> ''
ISNULL and NULLIF are pretty obvious about their meanings to me, too, though the enforced data typing isn't that transparent. Now, COALESCE is something that I'd never have found as the answer to the problem it solves without someone once having pointed it out to me.
I'm all for SARG-ability, but sometimes it's just not there. No matter what you do, you have to adjust the source for the comparison(s) in certain cases.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 18, 2013 at 5:42 pm
Evil Kraig F (12/18/2013)
Scott,I'm not sure how that's more or less obscure, but it's certainly easier for me to read. I work with a lot of 'iffy' data in loaders, and a structure like this is pretty common for me:
WHERE
ISNULL( LTRIM(RTRIM(a.field)), '') <> ''
ISNULL and NULLIF are pretty obvious about their meanings to me, too, though the enforced data typing isn't that transparent. Now, COALESCE is something that I'd never have found as the answer to the problem it solves without someone once having pointed it out to me.
I'm all for SARG-ability, but sometimes it's just not there. No matter what you do, you have to adjust the source for the comparison(s) in certain cases.
Yikes! Table values should be trimmed by a trigger once during insert/update if necessary rather than in every query afterward. If you really need to, you can store a count of the leading/trailing spaces originally present in separate columns.
Besides, if you're doing <> '', you don't need ISNULL() in the first place:
LTRIM(RTRIM(a.field)) <> ''
is functionally the same, since NULL won't ever be "not equal" to anything.
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".
December 18, 2013 at 5:43 pm
I'm truly curious now:
Can anyone tell me:
Where "IS NULL" can't be used in place of ISNULL()?
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".
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply