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, [email protected] 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 <[email protected]>
*Sender: * [email protected]
*Date: *Thu, 19 Jan 2012 09:32:58 +0800
*To: *ozDotNet<[email protected]>
*ReplyTo: * ozDotNet <[email protected]>
*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!
