Re: [OT] t-sql, dealing with null
thanks for that detailed response, very interesting. for the record the SP was not that short, it was something more complex buried deep within the stored proc. Isnull + coalesce in queries when you have to, but I try to write only positive queries, or queries against non null fields. Good advice, thanks again. On 19/01/2012 3:34 PM, djones...@gmail.com wrote: Hi, This is a bit long, and only my opinion after 20+ years of doing this stuff. If I'm designing the database, somecolumn will not allow nulls. select * from SomeTable where SomeColumn @SomeFlag It also wouldn't be used without a qualifying clause, it's too much data to return with a single query. select * from SomeTable where SomeColumn @SomeFlag and constrictiveData = @queryParam Would be half a step there. Given that I know the range of values for SomeColumn (all positive for example) select * from SomeTable where isnull(SomeColumn,-1) @SomeFlag and constrictiveData = @queryParam If some flag is a small discrete number of values ( status - open pending delivery delivered ) I would make the query positive. select * from SomeTable where SomeColumn in ( open, closed, pending, delivery, delivered ) If we are talking delivery tracking web page for example. The query would be. select top 5 * from SomeTable where constrictiveData = @queryParam Order by nonnullstatusDate desc And then let them pick which order to see. Isnull + coalesce in queries when you have to, but I try to write only positive queries, or queries against non null fields. As a side note, using prodecures for simple sql statements is a counter productive activity. Paramertised queries from the client side allow the developer to correct bugs much faster than a 100% stored procedure solution. It is also much cleaner in the long run. I currently maintain a legacy project with 753 stored procedures, there are about 20 named along the lines of Up_getLastPrice Up_getLastPriceBeforeDiscount Up_getLastPriceWithDiscount Up_getLastDiscountPrice Up_getDiscountPrice Etc. It's a nightmare, dependency checks tell me that every single last one of them is used somewhere, in the application all the sp's in the database are held as constants, the data gets bound directly to statically typed datasets. I've done lots of development using different languages and different design principles. At the moment I like Agile - Scrum, all tdd + integration testing. Support for bug corrections is trivial. The simple rule is KISS (Keep It Simple Stupid) Again only my experience and personal view on things. Davy. Hexed into a portable ouija board. *From: * Wallace Turner wallacetur...@gmail.com *Sender: * ozdotnet-boun...@ozdotnet.com *Date: *Thu, 19 Jan 2012 09:32:58 +0800 *To: *ozDotNetozdotnet@ozdotnet.com *ReplyTo: * ozDotNet ozdotnet@ozdotnet.com *Subject: *Re: [OT] t-sql, dealing with null Davy, assuming this code and your Rule Number 1, what do you do: |create procedure GetSomething ( @SomeFlag ) select * from SomeTable where SomeColumn @SomeFlag| (just to be clear this query will not return rows where SomeColumn is null, assuming @SomeFlag=1) On 18/01/2012 11:24 PM, David Rhys Jones wrote: Rule number 1, Don't do business logic in the database. .02c Davy, The US Congress voted Pizza sauce a vegetable. Don't even try to convince me of anything in the states is sane any more!
Re: [OT] t-sql, dealing with null
Oh and I forgot, you can write positive queries for negative results. ie select * from address where city 'New York' -- Never use this form. consider * 1. select * from address where not addressId in (select addressId from address where city = 'New York')* *2. select * from address where isnull(city,'') 'New York' * If the City field isn't index the 2nd query comes out best, If city field is indexed, then the 1st query is better. If you forget to index the field Sql Server Management Studio will even suggest the index. Davy, The US Congress voted Pizza sauce a vegetable. Don't even try to convince me of anything in the states is sane any more! On Thu, Jan 19, 2012 at 12:31 PM, Wallace Turner wallacetur...@gmail.comwrote: thanks for that detailed response, very interesting. for the record the SP was not that short, it was something more complex buried deep within the stored proc. Isnull + coalesce in queries when you have to, but I try to write only positive queries, or queries against non null fields. Good advice, thanks again.
RE: [OT] t-sql, dealing with null
You are checking a variable for equality with a literal value without having yet assigned a value. Also, with ANSI_NULLS on, you cannot use comparison type operators to check a variable for NULL as '= NULL' and ' NULL' will be 'unknown' when NULL values are involved. You must use 'is null' or 'is not null' instead (or functions like IsNull, NullIf, Coalesce). Of course you can use comparison type operators to compare non-NULL values. A bit variable can be NULL, or assigned the value NULL (unknown), 0 or 1 by a simple assignment or a complex select statement. Therefore, to test all possible cases try something like: declare @var bit -- a complex select statement or a simple assignment set @var = NULL -- try all of NULL, 0 or 1 or comment out the whole line if @var is not null if @var 1 print 'value is not equal to 1' else print 'value is 1' else print 'value is unknown' Hope that makes sense. Cheers, Chris From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Wallace Turner Sent: Wednesday, 18 January 2012 6:20 PM To: ozDotNet Subject: [OT] t-sql, dealing with null Consider: declare @var bit if (select @var) 1 print 'value is not equal to 1' else print 'value is 1' assuming ANSI_NULLS is on then the output is: value is 1 What are some common ways that people deal with this 'gotcha' ? eg 1) don't use operator at all 2) use ISNULL Please consider the case where the select is a (more) complex statement. Regards Wal
Re: [OT] t-sql, dealing with null
Hi Wallace, I am always using ISNULL function for these cases when I am to compare. Another possibility is to check for NULL explicitly: IF @var IS NULL BEGIN -- do stuff END ELSE BEGIN -- do other stuff END Cheers, jano On 18 January 2012 08:20, Wallace Turner wallacetur...@gmail.com wrote: Consider: declare @var bit if (select @var) 1 print 'value is not equal to 1' else print 'value is 1' assuming ANSI_NULLS is on then the output is: value is 1 What are some common ways that people deal with this 'gotcha' ? eg 1) don't use operator at all 2) use ISNULL Please consider the case where the select is a (more) complex statement. Regards Wal
Re: [OT] t-sql, dealing with null
Thanks for your responses, Surely there is a plethora of bugs keeping devs busy where someone has failed to consider the null case in a comparison which has unintended results ?! Especially considering the following c# code |int? x = null; if(x != 1) Console.WriteLine(x is not 1); else Console.WriteLine(x is 1);| and the 'same' thing in t-sql does the opposite... On 18/01/2012 4:52 PM, Jano Petras wrote: Hi Wallace, I am always using ISNULL function for these cases when I am to compare. Another possibility is to check for NULL explicitly: IF @var IS NULL BEGIN -- do stuff END ELSE BEGIN -- do other stuff END Cheers, jano On 18 January 2012 08:20, Wallace Turner wallacetur...@gmail.com mailto:wallacetur...@gmail.com wrote: Consider: |declare @var bit if (select @var) 1 print 'value is not equal to 1' else print 'value is 1' | assuming ANSI_NULLS is on then the output is: value is 1 What are some common ways that people deal with this 'gotcha' ? eg 1) don't use operator at all 2) use ISNULL Please consider the case where the select is a (more) complex statement. Regards Wal
Re: [OT] t-sql, dealing with null
Rule number 1, Don't do business logic in the database. .02c Davy, The US Congress voted Pizza sauce a vegetable. Don't even try to convince me of anything in the states is sane any more!
Re: [OT] t-sql, dealing with null
Davy, assuming this code and your Rule Number 1, what do you do: |create procedure GetSomething ( @SomeFlag ) select * from SomeTable where SomeColumn @SomeFlag| (just to be clear this query will not return rows where SomeColumn is null, assuming @SomeFlag=1) On 18/01/2012 11:24 PM, David Rhys Jones wrote: Rule number 1, Don't do business logic in the database. .02c Davy, The US Congress voted Pizza sauce a vegetable. Don't even try to convince me of anything in the states is sane any more!
Re: [OT] t-sql, dealing with null
Hi, This is a bit long, and only my opinion after 20+ years of doing this stuff. If I'm designing the database, somecolumn will not allow nulls. select * from SomeTable where SomeColumn @SomeFlag It also wouldn't be used without a qualifying clause, it's too much data to return with a single query. select * from SomeTable where SomeColumn @SomeFlag and constrictiveData = @queryParam Would be half a step there. Given that I know the range of values for SomeColumn (all positive for example) select * from SomeTable where isnull(SomeColumn,-1) @SomeFlag and constrictiveData = @queryParam If some flag is a small discrete number of values ( status - open pending delivery delivered ) I would make the query positive. select * from SomeTable where SomeColumn in ( open, closed, pending, delivery, delivered ) If we are talking delivery tracking web page for example. The query would be. select top 5 * from SomeTable where constrictiveData = @queryParam Order by nonnullstatusDate desc And then let them pick which order to see. Isnull + coalesce in queries when you have to, but I try to write only positive queries, or queries against non null fields. As a side note, using prodecures for simple sql statements is a counter productive activity. Paramertised queries from the client side allow the developer to correct bugs much faster than a 100% stored procedure solution. It is also much cleaner in the long run. I currently maintain a legacy project with 753 stored procedures, there are about 20 named along the lines of Up_getLastPrice Up_getLastPriceBeforeDiscount Up_getLastPriceWithDiscount Up_getLastDiscountPrice Up_getDiscountPrice Etc. It's a nightmare, dependency checks tell me that every single last one of them is used somewhere, in the application all the sp's in the database are held as constants, the data gets bound directly to statically typed datasets. I've done lots of development using different languages and different design principles. At the moment I like Agile - Scrum, all tdd + integration testing. Support for bug corrections is trivial. The simple rule is KISS (Keep It Simple Stupid) Again only my experience and personal view on things. Davy. Hexed into a portable ouija board. -Original Message- From: Wallace Turner wallacetur...@gmail.com Sender: ozdotnet-boun...@ozdotnet.com Date: Thu, 19 Jan 2012 09:32:58 To: ozDotNetozdotnet@ozdotnet.com Reply-To: ozDotNet ozdotnet@ozdotnet.com Subject: Re: [OT] t-sql, dealing with null Davy, assuming this code and your Rule Number 1, what do you do: |create procedure GetSomething ( @SomeFlag ) select * from SomeTable where SomeColumn @SomeFlag| (just to be clear this query will not return rows where SomeColumn is null, assuming @SomeFlag=1) On 18/01/2012 11:24 PM, David Rhys Jones wrote: Rule number 1, Don't do business logic in the database. .02c Davy, The US Congress voted Pizza sauce a vegetable. Don't even try to convince me of anything in the states is sane any more!
[OT] t-sql, dealing with null
Consider: |declare @var bit if (select @var) 1 print 'value is not equal to 1' else print 'value is 1' | assuming ANSI_NULLS is on then the output is: value is 1 What are some common ways that people deal with this 'gotcha' ? eg 1) don't use operator at all 2) use ISNULL Please consider the case where the select is a (more) complex statement. Regards Wal
