February 23, 2007 at 10:02 am
Does anyone have a viewpoint on where the logic for Select statements shuold sit.
I always write my Insert , Update & Deletes as stored procs that the developers call with parameters but when the select has many possibilities for sorting and selection I have left the develpers to embed the Select in the application logic.
Thanks
Allen
February 23, 2007 at 10:22 am
Always in stored procedures. SQL Injection is a good reason for this.
February 26, 2007 at 1:52 am
Thanks Steve, I'm probably being a bit thick...but how do you do it?
February 26, 2007 at 9:11 am
We require all application access to use Stored Procedures. In fact the app users do not Select, Insert, .. privileges at all.
When there is a complex select I use various Stored Procedure parameter techniques such as flags, codes, and selectors. This approach requires the developers to think about what they really want.
Typically the select procedure only does a portion of the logic. The Application's Business Layer or Client Side has lots of filtering and sorting available to tailor a general result set to differing display needs.
This approach generally follows MS Best Practices and on this topic I fully agree with them.
February 28, 2007 at 7:37 am
Thanks Steve & Ray
Just wondering how you handle the various bits of a select that might change for each execution when wrapped up as a stored proc (paticularly the order by and where clause). The only thing I can immagine is passing in strings to the proc which seems a bit kluncky or writing very specific stored procs.
Thanks
Allen
March 1, 2007 at 10:45 am
With the new ROW_NUMBER() feature in 2005 a lot of my select procedures have multiple select statements in them and only differ by the order by clause. So in order to minimize confusion I keep different kinds of select statements in different procedures and try to name them in such a way that it's obvious what they return.
So to answer your question I usually have many different select procedures for a given table.
Rob Mills
www.dotnetadvisor.comMarch 6, 2007 at 8:07 am
Always stored procs.
If the order or where clause is radically variable, you may have to generate a dynamic statement within the stored procedure (totally frowned on, but possibly necessary).
Think about it in terms of SOA. The contract for supplying information from the database is through the procedures.
The biggest "win" we've found for procs over inline sql statements is maintenance. If we, the dba's, identify a tuning opportunity, we can tweak the procedure, maintain the agreed upon contract (meaning, the same parameters in return the same exact set of data out) and deploy without being forced to compile, test & redeploy code. Even the anti-procedure developers have come around.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2007 at 8:50 am
I agree with using stored procs for most data access, the exception being truly dynamic cases like searches - those can typically be done client side more easily, but you can do it on the server if you prefer. The idea of procs equaling a contract is a great point and one I stress a lot, along with the point that if the select is not in a proc as a DBA all I can do is index to help you get performance. I can't rewrite the query to make it perform better, can't add lock, index, or join hints either. Having the ability to modify that layer without recompile/redeploy gives a DBA a lot of room to work.
As far as dynamically selecting columns or setting the order by, procs don't excel at that. Doesn't mean we can't write some cases to make it happen to a certain degree, but it tends to add complexity where before there was none! In most cases having the column list static is both a good idea (a contract) and matches what the developer needs, again the exception being searches. For a good search the column list should be dynamic along with everything else.
March 31, 2009 at 9:55 am
I use COalesce extensively to handle the possibility of many params when doing searches and things like that.
I don't prefess to be a SQL guru but I have found this technique to be very versitile. The only thing you have to watch for is when you use it with a field that is nullable in that case you have to put a coalesce around the field name with a default fall back value of the fields type on both sides of the =. Here's an example:
ALTER PROCEDURE [dbo].[get_lcm_laneclosure_Filter]
@Laneclosure_IDint = null,
@IncludeInactiveRequestSOurcesbit = null,
@Closure_Type_IDint = null,
@RequestSOurcesIDsXML = null,
@IncludeSpecialEventsbit = null ,
@DaytimeClosuresOnlybit = null,
@Status_IDint = null,
@RoadtobeClosed_IDint = null,
@BeginMPdecimal(6,3) = null ,
@EndMPdecimal(6,3) = null ,
@BICint = null,
@DirectionIDint = null,
@StartDateDateTime = null,
@EndDateDateTime = null,
@DateEnteredStartDateTime = null,
@DateEnteredEndDateTime = null,
@PrimarySortvarchar(50) = 'date_Created',
@PrimarySortDIrectionchar(4) = ''
AS
SELECT laneclosure_pk
from dbo.[LCM_V_LaneClosureNotices_All] LCN
Left Join dbo.LCM_LaneClosure_DirectionAffected DA on LCN.laneclosure_pk = DA.laneclosure_Fk
Where
LCN.laneclosure_pk = coalesce(Nullif(@Laneclosure_ID,0),LCN.Laneclosure_pk)
and LCN.ClosureType_pk =
case when @IncludeSpecialEvents =1 then 7
else Coalesce(Nullif(@Closure_Type_ID,0),LCN.ClosureType_pk)
End
and LCN.daytime_closure_request = Coalesce(Nullif(@DaytimeClosuresOnly,0),LCN.daytime_closure_request)
and LCN.laneclosure_status_fk = coalesce(Nullif(@Status_ID,0),LCN.laneclosure_status_fk)
and LCN.roadtobeclosed_pk = coalesce(Nullif(@RoadtobeClosed_ID,0),LCN.roadtobeclosed_pk)
and (LCN.Start_Milepost between Coalesce(Nullif(@BeginMP,0),Start_MilePost) and Coalesce(Nullif(@EndMP,0),End_MilePOst))
and (End_MilePOst between Coalesce(nullif(@BeginMP,0),Start_MilePost) and Coalesce(Nullif(@EndMP,0),End_MIlePost))
and ((date_of_closure >= coalesce(@StartDate,date_of_closure)) and (date_of_closure <= coalesce(@EndDate,date_of_closure)))
and ((date_created >= coalesce(@DateEnteredStart,date_created)) and (date_created <= coalesce(@DateEnteredEnd, date_created)))
and DA.laneclosure_direction_fk = coalesce(Nullif(@DirectionID,0),laneclosure_direction_fk)
if it's a nullable field then just do this:
Numeric FIeld:
Where Coalesce(FIeldName,1) = coalesce(Nullif(Param,0),FieldName,1)
String Field:
Where Coalesce(FIeldName,"1") = coalesce(Nullif(Param,''),FieldName,"1")
March 31, 2009 at 10:05 am
That kind of query is all too often a performance nightmare.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply