> > Yes, but you have to do more with the stored proc code. You must use a
> CASE
> > statement... here's an example:
>
> why do you need a CASE? if *build* your sql statement dynamically within
> your sp & EXECUTE or sp_executeSQL it there's no need.
One thing that I will comment on. The company that I work for is in the
process of changing coding standards on it's stored procedures and it falls
right in line with this thread.
We used to build SP's that would dynamically generate a @cSQL line,
something like
DECLARE @cSQL VARCHAR(5000)
SET @cSQL = 'SELECT u.fname, u.lname, u.uname, a.level from users u inner
join access a on u.access_id = a.access_id'
IF @users_id IS NOT NULL
SET @cSQL = @cSQL + 'WHERE u.users_id = ' + Convert(VarChar(20), @users_id
EXEC (@cSQL)
okay, that's a very very simple example... I can't post a more complex one,
rules and all.
Anyways, it turns out that a SP like the one above actually can turn into a
performance hog. The reason has to do with the execution plan of the SP.
When a static SP is created you end up having a static plan. With the one
above it has to generate the execution plan every time. At least that's how
our DBA explained it.
Granted, if it's low traffic or low record count, it's not going to amount
to a whole lot of difference.
Good Luck!
Hatton Humphrey
.
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
