Re: Help me convince the dev manager. Please.

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 05/13/04 

Date: Thu, 13 May 2004 07:31:12 -0500

> There are only three options - dynamic SQL, static SQL and a hybrid
solution.

Why not construct the SQL dynamically in your application code? IMHO, this
approach is easier to maintain than doing it in Transact-SQL. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Rohan Hattangdi" <RohanHattangdi2003@hotmail.com> wrote in message
news:OIouKJFOEHA.2480@tk2msftngp13.phx.gbl...
> 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)
  • Help me convince the dev manager. Please.
    ... I just came back from a meeting with the dev managers here and one of the ... to handle optional parameters. ... There are only three options - dynamic SQL, ... makes code difficult to read and to debug. ...
    (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.
    ... >to handle optional parameters. ... >always that there must be a better way to handle this because dynamic SQL ... "Best Practice" is to leave the house and call 911. ... >re-writing a sproc to show the manager how clearly structured dynamic SQL ...
    (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)