In response to the first post, you can do _anything_ you want with
Exec(@sSQL). If @sSQL contains multiple select statements, then your sp
will return multiple result sets.
In response to the most recent comment - although you do lose the advantage
of guaranteed pre-compilation, I have found many situations in which
building dynamic sql within a stored proc works very well for me. I do not
agree that it should be avoided - it has many valid uses, and has saved me
hours of coding time.
Bob
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 10, 2001 3:15 AM
To: CF-Talk
Subject: Re: OT STORED PROCEDURES
> I had thought of using Dynamic SQL in SQL Server stored procedures, but
> hadn't gotten around with playing with it much yet.
I just got done researching using Dynamic SQL and stored procedures. Bottom
line from the SQL guru community, it should be avoided. Main reason, dyn sql
forces the db to recompile the sp_proc, and as you may know, the reason you
use a sp_proc is the db can return the results faster because, as BOL
states, "... persistent storage of compiled Transact-SQL scripts".
It doesn't change the fact sp_proc can return more than one result set at a
time.
Your mileage may vary.
Night,
Rick
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
