OK im not an expert SQL user or anything and im trying to figure out how to
do stuff purely from BOL...Im still learning how to use dynamic SQL and the
sp_executesql. im trying to run some dynamic sql code on every column in
every user table in a database.... the general structure of the code i got
(sorry im sure its terrible) is like this:
declare @colname sysname
declare @colcurs cursor for
select syscolumns.name from syscolumns inner join sysobjects on syscolumns.id
= sysobjects.id
where sysobjects.xtype = 'u' and sysobjects.name != 'dtproperties'

open colcurs
fetch next from colcurs into @colname
while @@fetchstatus = 0
begin
{dynamic SQL code using @colname - im not putting this in yet until i see
that this whole thing works as i expect}
fetch next
end
close colcurs
deallocate colcurs

ok, if I just run the select, I get the rowset im looking for. WHen i try the
whole thing with the cursor, I get 'incorrect syntax near the keyword 'for''.
what am I doing wrong? something wrong with this cursor?
thanks dearly

--
Message posted via SQLMonster.com

sorry haha ok i figured it out, @colcurs should just be colcurs

--
Message posted via SQLMonster.com

[quoted text, click to view]

This is much like learning to cook or have sex from a book, isn't it?
:)

The first thing is that you want to avoid dynamic SQL in favor of
compiled SQL code and stored procedure.

Dynamic SQL says, "My understanding of the problem and my Software
Engineering skills are sooooo poor that a random future user knows more
on the fly than I do after all my research!!"

Cursors are even worse!! They say that you have absolutely no
understanding of declarative code and are reverting to 1950's
procedural code. In over 20 years of SQL, I have written five cursors
and i know that if I had CASE expressions, I could have easily avoided
three of them.

Dynamic SQL **and** a cursor is soooooooo damn wrong!!

Do you want to do this right or have code that runs 2 to 3 order of
magnitude slower than it could?

Your comments about dynamic SQL are just plain wrong.

Dynamic SQL is compiled, it does not run slower than static SQL, in fact the
execution plan is kept from the first invocation (just like static SQL) and
also that execution plan can be parameterised, SQL Server does some of this
for you.

Dynamic SQL can be used to great effect where you have multiple optional
parameters and you cannot build an efficient query plan, the only other way
would be to have countless IF ELSE statements for each possible parameter
combination or to have masses of stored procedures again for each possible
combination - that would be maintanence hell.

Cursors are bad if there is a set based solution available, but there isn't
always one available, also, there might be reasons for using a cursor - data
cleansing, creating export files; there is no real need to learn another 3gl
language when we have a very simple Transact SQL language that will do the
job and its done inside the engine, in SQL 2005 we have more power with CLR
intergration.

I would dearly love to see your performance statistics and benchmarks to
back up your claim that dynamic SQL is '2 to 3 order of magnitude slower',
this is several times i've asked you to post but you never do; perhaps you
should go and do some testing and come back once you've actually tried it
rather than making things up to try and bolster your position.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


[quoted text, click to view]