Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
John Obelenus
Starting Member
10 Posts |
 Posted - 2004-06-04 : 18:58:17
|
| OK, this one has been giving me a headache for hours...lets get acquainted with my setup first...here is the stored proc that is screwing up:CREATE PROCEDURE dbo.SP_CATCH(@passedval NVARCHAR, @uid BIGINT, @sid NVARCHAR) ASBEGIN DECLARE @ctpid INT, @tname NVARCHAR(50); SELECT @tname='BUTTON'+CAST(@uid AS NVARCHAR) SELECT @ctpid=MAX(ID) FROM TPERIOD WHERE UID=@uid UPDATE [@tname] SET EXITPAGE=0 WHERE SESSIONID=@sid INSERT INTO [@tname] (SESSIONID, BUTTONNAME, EXITPAGE, TPID, CLICK) VALUES (@sid, @passedval, 1, @ctpid, CAST(GETDATE() AS SMALLDATETIME))ENDGOI'm going to have a bunch of tables BUTTONX, where X=a number. That number is equal to a userid. So each user will have a table, so this query is designed for all users in general, therefore the FROM table, has to be a variable.If you check the INSERT INTO statement, you can see the table setup, just for some clarification:SESSIONID=NVARCHAR[50]BUTTONNAME=NVARCHAR[20]EXITPAGE=BITTPID=INTCLICK=SMALLDATETIMEAs it stands right now, at runtime I'm told the @tname variable is an "Invalid object name '@tname'." Line 7, which is the UPDATE statement. I was trying to get this working with exec, and with just trying out one table at a time, and the results were quite odd. Both the SESSIONID, and BUTTONNAME columns (NVARCHAR) will only store numbers, and one number at that. If I put a alphabetic character (or more) in to my stored proc, it would say that "You can't use 'x'(where x=alphabetic character) in this context".Anyone ever run into anything quite like this?FYI, this is going to be a large record table. I could have put a USERID column in, instead of making a table for each user. But I feel I would have a problem of running out of ID's down the line. This is going to be a statistics tracking database, and this table holds button clicks for each user. In other words, I chose BIGINT as an autoincrement for this table because I thought I would need it. |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-06-04 : 19:12:01
|
| JohnYou are going to run into some BIG problems.If you think you might run out of numbers for user IDs and dont want to store them in a column, how do you think SQL servr is going to cope with that many tables ?????If you really are worried about running out of identities in a column, use a GUID insteadGraham |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-04 : 19:18:33
|
| You can start the identity value with a negative number. So if you've got a BIGINT, just use:IDENTITY(-9,223,372,036,854,775,807, 1)So you'll be able to use 9,223,372,036,854,775,807 times 2 for your identity values.Tara |
|
|
|
John Obelenus
Starting Member
10 Posts |
Posted - 2004-06-07 : 07:32:14
|
| tduggan, you have a very good point... I think I am going to try to do that.. start with a negative bigint...gpl, it's not so much that I will have tons of tables.. at most I think (within a reasonable timetable), we could have 50-100 of those tables... but the problem is if a few of those tables, tracks a few hundred thousand hits a month, that puts alot of records in my table(if I only have one table).but just for the sake of me being very angry that I can't make a sql statement with a variable for a tablename, in the spirit of learning, I'd like to figure out why it won't work! :P |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-07 : 12:30:41
|
| To use a variable as the table name, you'll need dynamic sql.Tara |
|
|
|
Billpl
Yak Posting Veteran
71 Posts |
Posted - 2004-06-10 : 01:07:45
|
| It would be very cool to be able to assign a table (say from a UDF) to a variable and pass it around in a SP. |
|
|
|
|
|
|
|
|
Stored Proc, with table as variable