Re: Help me convince the dev manager. Please.

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

Date: Wed, 12 May 2004 16:22:27 -0400

Thank you.

Yes, I would use IF blocks for really simple sprocs with few optional
parameters.

I have already suggested that they use sp_executesql for the dynamic SQL
strings.

Rohan

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:%234IPNQFOEHA.3420@TK2MSFTNGP11.phx.gbl...
>
> "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.
> >
>
> As you have discovered, there is a difference of opinion about this.
>
> I actually prefer not to use dynamic SQL until you have more than a few
> optional parameters. For simple scenarios I use static SQL with IF blocks
> or the (@param = col or @param is null) construct.
>
> As the number of optional parameters and the subsets of them which might
be
> used become more complex, Dynamic SQL becomes more attractive. Just
> remember to use sp_executesql and paramtater binding for high-volume
> low-cost queries.
>
> David
>
>

Relevant Pages

  • 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.
    ... > 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.
    ... > 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: TSQL problem
    ... First of all, use single-quotes, not double quotes, for strings. ... you don't need to use dynamic SQL in this situation: ... > i want to execute my string in stored procedure ... > exec @query ...
    (microsoft.public.sqlserver.programming)
Loading