Planning on going with dynamic SQL, but...

[Ronald S. Cook]

I've read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.

Given that, I'm leaning toward dynamic sql mostly because it would mean one
fewer place to have things.

But, before we go that route we wanted to ask the question:

Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?

Or, is it just preference these days?

I was leaning toward procs, but I have to admit it would be nice not to have
to keep up with all of them per all of our functions that call them.

Thanks,
Ron

[rowe_newsgroups]

Re: Planning on going with dynamic SQL, but...

On Jun 13, 7:01 pm, "Ronald S. Cook" <r...@westinis. comwrote:

I've read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.
>
Given that, I'm leaning toward dynamic sql mostly because it would mean one
fewer place to have things.
>
But, before we go that route we wanted to ask the question:
>
Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?
>
Or, is it just preference these days?
>
I was leaning toward procs, but I have to admit it would be nice not to have
to keep up with all of them per all of our functions that call them.
>
Thanks,
Ron

I like sprocs because I can change what a procedure does without
having to recompile and redistribute the application. ClickOnce takes
care of a lot of the hassle, but many of my applications are long
running and aren't restarted very often, so it might take a while for
the updates to all take affect. If I use a stored procedure than when
I update it it affects all users, immediately. To me, it depends on
what the application is going to do, how many updates I can expect,
what types of updates there are going to be, and the environment the
application will run in.

Just my two cents.

Thanks,

Seth Rowe

[Spam Catcher]

Re: Planning on going with dynamic SQL, but...

"Ronald S. Cook" <rcook@westinis .comwrote in
news:#73a35grHH A.192@TK2MSFTNG P02.phx.gbl:

Is there any compelling reason why we shouldn't abandon all of our
stored procs and just write the SQL inside inside our functions in our
business layer (essentially our data access layer)?
>
Or, is it just preference these days?

It's preference. I like to compile my SQL into the code.

However, SPs could be safer due to the use of parameters which prevent SQL
injection attacks - but if you write your SQL correctly by using
SQLParameters you'll be fine either way.

P.S. check out a data access layer generator like LLBLGen Pro - they make
writing dynamic SQL a lot easier (no need to write SQL, everything is
code).

[Andrew Morton]

Re: Planning on going with dynamic SQL, but...

Ronald S. Cook wrote:

I've read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.

"The Curse and Blessings of Dynamic SQL"

http://www.sommarskog.se/dynamic_sql.html

The part that was of most interest to me is that if you use parameters with
dynamic SQL then execution plans can be re-used, if that's what you're
hinting about performance issues.

Andrew

[AlexS]

Re: Planning on going with dynamic SQL, but...

About "wash" - depends what tests you did and how.
Generally speaking spocs are faster by definition, because db engine is
expected to prepare execution plan during procedure creation, not during run
time. With dynamic sql you always have additional processing for analysis
and selection of plan. Which might be not that important when you don't
process heavy volumes. I see always a speedup with sprocs. Not much, but
nevertheless.

About last point - I believe you have to keep up with all of them - be it
sprocs, or dynamic statements and maintenance effort is approximately same.
But SP you can modify during run-time and for all clients in one step, which
is impossible for hard-coded stuff

"Ronald S. Cook" <rcook@westinis .comwrote in message
news:%2373a35gr HHA.192@TK2MSFT NGP02.phx.gbl.. .

I've read a few posts on the stored procedure vs dynamic sql debate. I
ran a few performance test for myself and it appears to be a wash.
>
Given that, I'm leaning toward dynamic sql mostly because it would mean
one fewer place to have things.
>
But, before we go that route we wanted to ask the question:
>
Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?
>
Or, is it just preference these days?
>
I was leaning toward procs, but I have to admit it would be nice not to
have to keep up with all of them per all of our functions that call them.
>
Thanks,
Ron
>

[Brian Gideon]

Re: Planning on going with dynamic SQL, but...

On Jun 13, 6:01 pm, "Ronald S. Cook" <r...@westinis. comwrote:

I've read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.
>
Given that, I'm leaning toward dynamic sql mostly because it would mean one
fewer place to have things.
>
But, before we go that route we wanted to ask the question:
>
Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?
>
Or, is it just preference these days?
>
I was leaning toward procs, but I have to admit it would be nice not to have
to keep up with all of them per all of our functions that call them.
>
Thanks,
Ron

My preference is stored procedures mostly because it allows me to
change the logic of the database interaction without recompiling,
testing, and deploying a new version of the application. The other
advantages are just not as important me.