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
