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: ozDotNet<ozdotnet@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! >
