Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question 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/12.0.0.6/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?

  2. #2
    Join Date
    Dec 2003
    Location
    Mumbai, India
    Posts
    2
    put a space before ur "order by" and then try

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    If I put a space before the order by, will the resulting table be available to the rest of my stored procedure?

    Is there a way to use the same command with the variable for the table name without using an exec statement?

  4. #4
    Join Date
    Mar 2004
    Posts
    3

    And the Saga continues

    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
    WITH RECOMPILE
    AS

    DECLARE
    @rec int,
    @count int,
    @new_recnum int

    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 */
    OPEN ccrcurs

    /* Step through the tmp table and update SMC_RECNUM */

    while (@@sqlstatus=0)
    begin

    /* first get a record from ccr_@table_name */
    fetch ccrcurs

    /* increment the counter */
    select @count = @count+1

    /* update only ccr table with @count */
    update ccrtmptbl set SMC_RECNUM=@count WHERE CURRENT OF ccrcurs

    end

    /* Close and Deallocate the cursor */
    CLOSE ccrcurs
    DEALLOCATE CURSOR ccrcurs

  5. #5
    Join Date
    Dec 2003
    Location
    Mumbai, India
    Posts
    2

    Re: And the Saga continues

    Try the same by declaring the cursor for update


    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 */
    print @sql_str
    EXECUTE IMMEDIATE (@sql_str)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •