I guess it depends on what you consider "unmanageably" large. I probably
wouldn't break the data out into multiple tables until I got into having
hundreds of thousands of events every month. I would make sure that my
tables are in good, solid 3NF and run the index tuning wizard a few times
to make sure I have the right combination of indices. If I found that I
had to partition the data like in your description, I would implement it in
SQL Server 2000 as a partitioned, updatable view so that the tables were
partitioned but my application still only needed to access one table.
|--------------------+----------------------------------------------------|
|Eric A. Laney |"Loyalty to petrified opinion never broke a chain or|
|Systems Architect |freed a human soul." |
|Verizon Security | |
|Voice: 813.987.1416 | |
|Pager: 888.551.3718 | Mark Twain (1835-1910)|
|--------------------+----------------------------------------------------|
"Diana Nichols"
<nichols2000@mind To: SQL <[EMAIL PROTECTED]>
spring.com> cc:
Subject: RE: dynamic table name in SP?
03/20/2002 09:19
AM
Please respond to
sql
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
