dynamic sql performance

i`m wondering how fast r stored procedures which define their insert,
select, where, from ... clauses as strings vs normal procedures?
at least i`d think dynamic sql gets compiled on every runPerformance here should not be at the top of your concern list, IMHO.
Please read:
http://www.sommarskog.se/dynamic_sql.html
"Fred" <fred@.ilovespam.com> wrote in message
news:euqdamdJGHA.3896@.TK2MSFTNGP15.phx.gbl...
> i`m wondering how fast r stored procedures which define their insert,
> select, where, from ... clauses as strings vs normal procedures?
> at least i`d think dynamic sql gets compiled on every run|||If you must use dynamic SQL, then build it at the application level and then
submit it via Execute or RowSet.Open. Building long queries or T-SQL within
a stored procedure is clunky.
"Fred" <fred@.ilovespam.com> wrote in message
news:euqdamdJGHA.3896@.TK2MSFTNGP15.phx.gbl...
> i`m wondering how fast r stored procedures which define their insert,
> select, where, from ... clauses as strings vs normal procedures?
> at least i`d think dynamic sql gets compiled on every run|||"JT" <someone@.microsoft.com> wrote in message
news:uvRhNWeJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> If you must use dynamic SQL, then build it at the application level and
> then submit it via Execute or RowSet.Open. Building long queries or T-SQL
> within a stored procedure is clunky.
>
No. All dynamic SQL is somewhat clunky. And TSQL is better for dynamic SQL
than most languages since it allows newlines in literal strings. EG
declare @.sql varchar(8000)
set @.sql = '
select * from
' + @.MyTable + '
where id = 124
and region in (' + @.RegionList + ')
'
Anyway you would just push the clunkyness out into the application. It's
generally simpler and more secure to keep the dynamic SQL close.
David|||Fred wrote:

> i`m wondering how fast r stored procedures which define their insert,
> select, where, from ... clauses as strings vs normal procedures?
> at least i`d think dynamic sql gets compiled on every run
Fast relative to what exactly? If you can do the same thing with static
SQL then you should. You use dynamic code when there is no viable
static alternative.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||For the sake of clarity, can you explain further just what
you mean by 'All dynamic SQL is somewhat clunky'.
Terms like 'clunky' can often hide very interesting points of view.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uqD3ZEfJGHA.1088@.tk2msftngp13.phx.gbl...
> "JT" <someone@.microsoft.com> wrote in message
> news:uvRhNWeJGHA.2696@.TK2MSFTNGP14.phx.gbl...
T-SQL
> No. All dynamic SQL is somewhat clunky. And TSQL is better for dynamic
SQL
> than most languages since it allows newlines in literal strings. EG
> declare @.sql varchar(8000)
> set @.sql = '
> select * from
> ' + @.MyTable + '
> where id = 124
> and region in (' + @.RegionList + ')
> '
>
> Anyway you would just push the clunkyness out into the application. It's
> generally simpler and more secure to keep the dynamic SQL close.
> David
>|||RE:
<< Terms like 'clunky' can often hide very interesting points of view.>>
And terms like that frequently hide often very UNinteresting points of view.
It's a form of name-calling. It's like referring to Microsoft as M$... or
that SQL Server is clunky... the CLR is bloat-ware. yadda yadda yadda. Often
these terms are used by luddites
(http://www.google.com/search?hl=en&...ite&btnG=Search).
"05ponyGT" <nospam@.nospam> wrote in message
news:uLHiHcgJGHA.964@.tk2msftngp13.phx.gbl...
> For the sake of clarity, can you explain further just what
> you mean by 'All dynamic SQL is somewhat clunky'.
> Terms like 'clunky' can often hide very interesting points of view.
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uqD3ZEfJGHA.1088@.tk2msftngp13.phx.gbl...
> T-SQL
> SQL
>|||Thanks for speaking up for Mr. luddite...er Mr. Browne.
I had no idea he was so hostile.
"Smithers" <A@.B.COM> wrote in message
news:OSkddjgJGHA.2912@.tk2msftngp13.phx.gbl...
> RE:
> << Terms like 'clunky' can often hide very interesting points of view.>>
> And terms like that frequently hide often very UNinteresting points of
view.
> It's a form of name-calling. It's like referring to Microsoft as M$... or
> that SQL Server is clunky... the CLR is bloat-ware. yadda yadda yadda.
Often
> these terms are used by luddites
> (http://www.google.com/search?hl=en&...ite&btnG=Search).
>
>
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:uLHiHcgJGHA.964@.tk2msftngp13.phx.gbl...
and
dynamic
It's
>|||"05ponyGT" <nospam@.nospam> wrote in message
news:uLHiHcgJGHA.964@.tk2msftngp13.phx.gbl...
> For the sake of clarity, can you explain further just what
> you mean by 'All dynamic SQL is somewhat clunky'.
Sure. 'Clunky' was introduced into the thread by JT complaining about
dynamic SQL in stored procedures. I took it to mean that TSQL stored
procedures that use dynamic SQL are not as simple and elegant as stored
procedures which use only static SQL. And dynamic SQL is clunky because you
have two different programs interlaced in one programming unit, where one
program is embedded as a literal string in the other program. That's
clunky.
But it's even more clunky when you embed a SQL program (query) in a 3GL
language like VB or C#. Not only are you building one program as a string
literal in another program, but the programming languages are different, and
the host language often doesn't allow literal strings to span multiple
lines. That's extra clunky.
David|||Please forgive my clunky analysis of the situation.