Prev: INSERT statement conflicted with COLUMN CHECK constraint.
Next: LEFT JOIN and unnecessary reads
From: Plamen Ratchev on 7 Apr 2010 21:59
Yes, the CASE expressions will work, you just build each query in the loop and execute. But instead of that why not
simply run four separate queries? Looking in more detail at your code now, there is really no reason to build
dynamically the queries. You do not save anything by writing less code, using four update queries is better. My first
response after you posted the code was incorrect - there is no need to use IF statements or separate stored procedures
because you want to execute all 4 queries.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 8 Apr 2010 14:22

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:n9CdnSaU3aQLpSDWnZ2dnUVZ_sWqnZ2d(a)speakeasy.net...
> Yes, the CASE expressions will work, you just build each query in the loop
> and execute. But instead of that why not simply run four separate queries?
> Looking in more detail at your code now, there is really no reason to
> build dynamically the queries. You do not save anything by writing less
> code, using four update queries is better. My first response after you
> posted the code was incorrect - there is no need to use IF statements or
> separate stored procedures because you want to execute all 4 queries.
>
That was one of the options I as looking at.

I thought maybe it might be better to do the same query as dynamic more as a
maintenance issue, not so much that it was less code. If you make a change
to one, you have to make sure you make the change to all 4. But I was
concerned with whether the optimizer would have to re-calculate the plan
each time it ran it.

If that isn't the case, it may be better, as you say, to do the 4 queries
separately.

Thanks,

Tom

> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 8 Apr 2010 16:43
Again, when you use sp_executesql with parameterized query SQL Server will place the plan in cache and reuse it. But I
think dynamic SQL can create more maintenance issues as it is not clear and easy to understand. Using four queries will
be simple and trivial to maintain, plus avoids all of the pitfalls of dynamic SQL.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 8 Apr 2010 21:55

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:zqednZzKyd5xoiPWnZ2dnUVZ_gYAAAAA(a)speakeasy.net...
> Again, when you use sp_executesql with parameterized query SQL Server will
> place the plan in cache and reuse it. But I think dynamic SQL can create
> more maintenance issues as it is not clear and easy to understand. Using
> four queries will be simple and trivial to maintain, plus avoids all of
> the pitfalls of dynamic SQL.
>
I agree.

Reading Erlands article helped to clarify the issues.

Thanks,

Tom

> --
> Plamen Ratchev
> http://www.SQLStudio.com


First  |  Prev  | 
Pages: 1 2
Prev: INSERT statement conflicted with COLUMN CHECK constraint.
Next: LEFT JOIN and unnecessary reads