In order to use a dynamic table name, you'll have to build the SQL in a
text string and then EXEC the string. However, doing this will negate the
benefit of using a stored procedure, as the SQL engine will have to
recompile its execution plan each time. If possible, re-evaluate your
system design with an eye to why you need dynamic table names. Chances
are, there's another way to do it, perhaps using a view (since you're only
doing a SELECT in your example). If that's not possible, you'll probably
be just as well off using a plain, old CFQuery.
|--------------+----------------------------------------------------------|
|Eric A. Laney |A man either lives life as it happens to him, meets it |
|Systems |head-on and licks it, or he turns his back on it and |
|Architect |starts to wither away. |
|Verizon | |
|Security | |
|Voice: |Dr. Boyce, "The Menagerie" ("The Cage"), stardate unknown.|
|813.987.1416 | |
|Pager: | |
|888.551.3718 | |
|--------------+----------------------------------------------------------|
"Diana Nichols"
<nichols2000@mind To: SQL <[EMAIL PROTECTED]>
spring.com> cc:
Subject: dynamic table name in SP?
03/20/2002 08:48
AM
Please respond to
sql
How can I pass a dynamic table name to a stored procedure?
Here's my query:
<CFSTOREDPROC PROCEDURE="GetEvent" DATASOURCE="#dsn#">
<CFPROCRESULT NAME="events">
<CFPROCPARAM TYPE="IN" VARIABLE="userIDIN" DBVARNAME="userIDIN"
VALUE="#userID#" CFSQLTYPE="cf_sql_integer">
<CFPROCPARAM TYPE="IN" VARIABLE="companyIDIN" DBVARNAME="companyIDIN"
VALUE="#companyID#" CFSQLTYPE="cf_sql_integer">
<CFPROCPARAM TYPE="IN" VARIABLE="eventTable" DBVARNAME="eventTable"
VALUE="#eventtable#" CFSQLTYPE="cf_sql_char">
</CFSTOREDPROC>
and here's the procedure:
CREATE PROCEDURE events @userIDIN int, @companyIDIN int, @eventTable
varchar(10)
AS
SELECT e.eventID, e.event, e.description, e.starttime, e.endtime,
e.location, e.url, e.priority, e.category, e.contactID, e.leadID,
e.leadname, e.contactname, e.done, e.eventdate, c.phone as cphone
FROM leads l RIGHT JOIN (@eventTable e LEFT JOIN contacts c on c.contactID
=
e.contactID) ON e.leadID = l.leadID
WHERE e.userID = @userIDIN AND datepart(day,e.eventdate) = DATEPART(day,
GETDATE())
ORDER BY e.starttime
A syntax check returns: Error137 must declare the variable "eventTable".
TIA!
D
************************
Diana Nichols
Webmistress
http://www.lavenderthreads.com
770.434.7374
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
