Help me convince the dev manager. Please.

From: Rohan Hattangdi (RohanHattangdi2003_at_hotmail.com)
Date: 05/12/04 

Date: Wed, 12 May 2004 14:50:35 -0400

All,

I cannot believe that I am posting about this again.

I just came back from a meeting with the dev managers here and one of the
dev managers insists that there must be another method out there to handle
optional parameters. I have gone over the article by Erland Sommarskog and,
both in conjunction with the article and via independent research, and
through personal experience, firmly believe that dynamic SQL is the best way
to handle optional parameters.

There are only three options - dynamic SQL, static SQL and a hybrid
solution. I keep mentioning that dynamic SQL is the industry-standard and
the preferred method for handling optional parameters but the response is
always that there must be a better way to handle this because dynamic SQL
makes code difficult to read and to debug. I came back with the fact the
dynamic SQL code is not that hard to read if structured correctly (code
style and standards) and is easy to debug if all parameters are passed in to
ensure that the SQL string is formed correctly. Unit testing must inherently
include the testing of all the parameters anyway. On top of that, correctly
structured SQL will ensure that common mistakes in string concatenation will
be avoided. Also, the QA process should take care of ensuring that if not
all possible combinations, at least all logical combinations will be tested
before the sproc makes it out to the real world.

I myself am happy with the article by Erland Sommarskog - he is a MVP and
his article includes suggestions by other MVPs and perhaps most important of
all, the article makes sense.

Is there anywhere else I can go to prove that dynamic SQL is the best?
Something from Microsoft? Something with a nice big official stamp that
says "Best Practice" or something similar?

I am out of options. I cannot think of any other way to convince this dev
manager. I cannot stand by and watch this poor practice continue. I am
re-writing a sproc to show the manager how clearly structured dynamic SQL
can be both easy to read and to debug.

Please, if anyone out there has something on this, I would really appreciate
it if you would help.

TIA,

Rohan

Relevant Pages

  • Re: Help me convince the dev manager. Please.
    ... > dev managers insists that there must be another method out there to handle ... > to handle optional parameters. ... I actually prefer not to use dynamic SQL until you have more than a few ... or the (@param = col or @param is null) construct. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help me convince the dev manager. Please.
    ... > dev managers insists that there must be another method out there to handle ... firmly believe that dynamic SQL is the best way ... > to handle optional parameters. ... Correctly structured SQL will also allow you to avoid dynamic sql in 99% ...
    (microsoft.public.sqlserver.programming)
  • Re: Help me convince the dev manager. Please.
    ... > There are only three options - dynamic SQL, static SQL and a hybrid ... > dev managers insists that there must be another method out there to handle ... firmly believe that dynamic SQL is the best ... > to handle optional parameters. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help me convince the dev manager. Please.
    ... SQL - not just in the fact that it's not precompiled, ... > dev managers insists that there must be another method out there to handle ... firmly believe that dynamic SQL is the best ... > to handle optional parameters. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help me convince the dev manager. Please.
    ... I would use IF blocks for really simple sprocs with few optional ... I have already suggested that they use sp_executesql for the dynamic SQL ... strings. ... >> dev managers insists that there must be another method out there to ...
    (microsoft.public.sqlserver.programming)