Thanks for the advice! The reason I use dynamic table names is to store events by month. My reasoning was that with multiple users storing multiple events in one table, it would become unmanageably large very quickly....so I have tables named event0102, event0202 (mmyy). Is there a better way to organize this data?
D ************************ Diana Nichols Webmistress http://www.lavenderthreads.com 770.434.7374 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 9:01 AM To: SQL Subject: Re: dynamic table name in SP? 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 ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
