| Author |
Topic |
|
Daver
Starting Member
5 Posts |
 Posted - 2004-01-28 : 10:27:48
|
| I am trying to create a stored procedure that will use a table name as one of the input parameters. In turn, this table name will be the value I want to use in my FROM clause for a cursor I use in the procedure. (I would like this one procedure to work with several tables, thus the need for a variable table name)Does SQL Server let me use a variable in my cursor SELECT statement? (It seems to be OK to use a variable in the Where clause).The syntax for my cursor is:DECLARE update_cursor CURSOR FOR SELECT * FROM @tablename WHERE EmployeeID = @emplidThe error I get says I "Must declare the variable @tablename". But @tablename is being passed as an input parameter to the procedure, so it is declared.Any insight would be appreciated. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-01-28 : 10:34:37
|
| research Dynamic SQL....and also try (seriously) to avoid cursors....some research here on "cursors + performance" will give you some reasons.... |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-28 : 12:23:29
|
| First of all, don't use cursors. Second, don't use dynamic sql. Each has performance problems. If you put them together, your whole system is going to suffer.What does your query need to do?Tara |
|
|
|
Daver
Starting Member
5 Posts |
Posted - 2004-01-28 : 12:37:35
|
| I appreciate the concern over performance but I really need the flexibility. I need to update a field that exists in several hundred tables. Within each table, there could be several rows that contain the field value I need to update. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-28 : 12:57:52
|
quote:
Originally posted by Daver
I appreciate the concern over performance but I really need the flexibility. I need to update a field that exists in several hundred tables. Within each table, there could be several rows that contain the field value I need to update.
You mean columns, not rows don't you?Also If you need to update all of these tables, how do you plam to do that? How do you get the table name? How do you get the columns, How do you get the value for each row to update? How do you ge the Key for the PK to update the row?If it's a simple a resetting a column to a default (like 0) then you don't have to worry about the pk....If that's true. then I don't think (damn I hate when that happens) you need a cursor...if you do then...I don't know what to tell you...Brett8-) |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-28 : 14:52:36
|
quote:
I need to update a field that exists in several hundred tables. Within each table, there could be several rows that contain the field value I need to update.
This almost never happens with proper database design, which explains the skeptics you see here in this thread. Also, use "column" instead of field; that's an old ISAM term that doesn't apply to relational systems.The responses so far properly point out that even if you had a procedure ready and working, you would still need to generate a list of tables to apply it to, and at that point it's no more effort to simply generate the UPDATE statements for each table and avoid calls to the procedure altogether.JonathanGaming will never be the same |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-28 : 15:23:10
|
| Yeah, what he said....(again)...but I'm sure there's a solution (albeit not a nice one) for you...Let us know...and Why SELECT * in the cursor?I really think you don't need one.....let us know with a concreate example...DDL and sample data would also help...I'm sure we can hook you up.....Brett8-) |
|
|
|
Daver
Starting Member
5 Posts |
Posted - 2004-01-28 : 16:28:19
|
Okay, at the risk of wasting your time (fair warning!), here's some more detail to my rather unique situation.I am working on an application written in SQR (a reporting and quasi-database programming language). SQR supports all the major RDBMS platforms although I currently only work with Oracle and SQL Server.Part of the application requires me to update a COLUMN  called EmployeeID. This column exists on several hundred tables, which I have a list of. On some tables EmployeeID is part of the key, on others it's not.SQR does allow the execution of SQL commands, so I could simply issue UPDATE statements and be done with it. However, SQR does not gracefully handle 'duplicate key' errors that may result from an update that breaks the uniqueness constraint.The way around this is to use the native database's SQL implementation language to build a cursor to work with rowsets from each table. This lets me capture the 'duplicate key' condition and still successfully update the other row(s) on the table with the same EmployeeID.All I have is the EmployeeID value to select on in my Where clause, so I need to retrieve all rows where EmployeeID = some_value , even if EmployeeID is only part of the key for the given table. I have a working solution for Oracle using PL/SQL that allows me to create a cursor with a variable table name (SELECT...FROM tablename WHERE...) and I was hoping SQL Server would let me do the same thing and loop through all the table names.And yes, I've posted to the SQR user group to see if anyone there works with SQL Server and may have some ideas. No responses there. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-28 : 16:45:59
|
| ok...post the oracle code....you didn't have any problems with updating the keys?Is the database set up to cascade?Brett8-) |
|
|
|
Daver
Starting Member
5 Posts |
Posted - 2004-01-28 : 17:02:00
|
| Here is the code I have for Oracle PL/SQL inside of SQR...DECLAREtmpField varchar2 (13);;dupVal boolean;;msg varchar2 (125) := $text;;fld varchar2 (1);;cursor upd1 is SELECT ($FieldName) FROM ($TableName) WHERE ($FieldName) = $emplid FOR UPDATE;;BEGINdupVal := FALSE;;OPEN upd1;;LOOPfetch upd1 into tmpField;;exit when upd1%NOTFOUND;;BEGIN UPDATE ($TableName) SET ($FieldName) = $mvemplid WHERE CURRENT OF upd1;;EXCEPTIONwhen DUP_VAL_ON_INDEX then dupVal := TRUE;; $flag := 'Y';; DELETE FROM ($TableName) WHERE CURRENT OF upd1;;when OTHERS then $ReturnFlg := '2';;END;;end loop;;CLOSE upd1;;END;; |
|
|
|
Daver
Starting Member
5 Posts |
Posted - 2004-01-30 : 09:50:42
|
| Solution to using variables in a cursor declaration (all variables are passed as input parameters to the stored proc creating the cursor):EXECUTE ('DECLARE empl_cursor CURSOR FOR SELECT ' + @fieldname + ' FROM ' + @tablename + ' WHERE ' + @fieldname + ' = ' + @emplid + ' FOR UPDATE')Just wanted to close this topic...thanks. |
|
|
|
nfsoft
Starting Member
36 Posts |
Posted - 2004-02-10 : 06:03:25
|
| What if you are in a trigger... Can you still refere to the table inserted and deleted?You can not. I assume you have to use somthing like select * into #ins from inserted select * into #del from deletedIs there a high performance problems? If so what to do?Nuno Ferreira |
|
|
|
|
Can I use a variable table name in my cursor?