Unanswered: stored procedures with variable table names
I am trying to write a stored procedure that will use a variable for the table name. The proc will contain two cursors that perform different functionality.
I've been beating my head against the wall for a week now and can't seem to get the majority of my commands to work with the variable for the table name. Am I just crazy for every trying this? I've been looking around the web for most of the day trying to find an answer and I've seen the same question from a lot of people, but haven't found a good answer.
I'm using Adaptive Server Enterprise/18.104.22.168/P/EBF 10628 ESD 1/Sun_svr4/OS 5.7/1918/64bit/FBO/Fri Oct 18 14:36:20 2002
With commands like:
EXECUTE IMMEDIATE ("SELECT * INTO ccrsorttmp FROM " + @ccrtbl + "ORDER BY ASGNT_DATE")
Also, is there a way to do the same thing without the exec so I'm still in the same session as my proc?
I've rewritten the stored procedure to not use variable table names. I never could get the variable names to be used correctly and I kept running into name space issues with my cursor. So, after rewriting this procedure to look like the attached, and having run it a few times, I seem to be running into a new problem. The procedure works great for the first table, but throughout the process (can be run against as many as 500 tables where ccrtmptbl is created by doing "SELECT * into ccrtmptbl from XXXX ORDER BY XXX") some tables just magically fail in the update of the SMC_RECNUM field.
After the proc runs, I do a select SMC_RECNUM from ccrtmptbl and the SMC_RECNUM list goes sequentially for a while and then seems to skip, start over, and create duplicates. Not the behavior I'm looking for.
I'm guessing I either need to add commit tran logic or start dumping the transaction log at the end of each run of the proc...but like I said that's only a guess. If anyone can shed some light onto what might be happening I'd appreciate it.
CREATE PROCEDURE collect_ccr_SORT
SELECT @count = 0
/* Declare cursor to sequentially update SMC_RECNUM in tmp table */
DECLARE ccrcurs CURSOR FOR SELECT SMC_RECNUM FROM ccrtmptbl
/* Open the cursor for use */
/* Step through the tmp table and update SMC_RECNUM */
/* first get a record from ccr_@table_name */
/* increment the counter */
select @count = @count+1
/* update only ccr table with @count */
update ccrtmptbl set SMC_RECNUM=@count WHERE CURRENT OF ccrcurs
/* Close and Deallocate the cursor */
DEALLOCATE CURSOR ccrcurs
Regarding ur initial problem of using variable names, what I suggested was some thing like following:
DECLARE @sql_str char(100)
DECLARE @ccrtbl char(20)
select @ccrtbl = "ur_table_name"
@sql_str = "SELECT * INTO ccrsorttmp FROM " + @ccrtbl + " ORDER BY ASGNT_DATE" /* please see that I put a space before order by, not with the table name. */
/* Print the out put */
/*Do this allways, from which u wil come to know the syntax/space issues, before u execute the immediate sqls */
EXECUTE IMMEDIATE (@sql_str)