You can do dyanmic stored procedures in SQL Server unless in your stored pr
oc, you set a variable inside the stored proc and then execute the stored p
roc...(i forget the exact syntax)
I tried to do dynamic stored procs and this is what I had to do to make it
work.
Then down side to this is that you will have the same stored proc in memory
twice. So if you do this in alot if stored procs, you will incurr twice th
e memory usage.
HTH
Clint
---------- Original Message ----------------------------------
from: "stas" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
date: Thu, 3 Jan 2002 13:12:01 -0500
I am using <cfstoredproc> so I won't be 'execing' anything. I need to know
how to costruct a dynamic ORDER BY (or a WHERE for that matter) clause for
the query. Do I glue it together in CF and then pass the whole line to the
SQL Server, or do I pass several different variables. In other words, how
would you convert a query like this to a stored procedure, and then how
could you call it with <cfstoredproc>?
<cfquery>
SELECT id
FROM tblOrder
<cfif isdefined("attributes.sortBy")>
ORDER BY #sortBy#
</cfif>
<cfif isdefined("attributes.sortOrder")>
#attributes.sortOrder#
</cfif>
</cfquery>
Thanks for any help and pointers.
----- Original Message -----
From: "Don Vawter" <[EMAIL PROTECTED]>
You can certainly do it in sql server. I can't say for other engines.
Just have the sp build the sql string and then have
exec (@st)
after you build the string.
----- Original Message -----
From: "stas" <[EMAIL PROTECTED]>
> I have a dynamically generated ORDER BY clause (as in ORDER BY
> #attributes.orderByClause# ). Is there a way to pass that on to a stored
> procedure if I wanted to convert a <cfquery> to <cfstoredproc>? Thanks!
______________________________________________________________________
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=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
