Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Unanswered: dynamic tablename issue

    Dear all:
    I have a storedprocedure for db loader form some temp tables.
    my storedprocedure as the following:

    declare c cursor
    for
    select * from @tablename
    open c
    fetch c into ....

    but I get an error for declare cursor for dynamic tablename,

    did sql server has some BIF to evaluate the variable for table name?

    thanks for your kindly assistance.

    regards,

    Stanley Huang

  2. #2
    Join Date
    Dec 2003
    Posts
    31
    use the dynamic sql statments EXEC(@sql_statement) or sp_executesql see the Holy BOL

  3. #3
    Join Date
    Apr 2004
    Posts
    5
    Originally posted by yoavmaimon
    use the dynamic sql statments EXEC(@sql_statement) or sp_executesql see the Holy BOL
    do you mean I can write the stored procedure as:

    create proc sp_dynamicTableName
    (
    declare @tablename varchar(20)
    )
    as
    declare c cursor
    for
    exec('select * from ' & @tablename)
    open c
    fetch c into ...

    regards for your reply

    thanks

    Stanley Huang

  4. #4
    Join Date
    Dec 2003
    Posts
    31
    what u wrote wont work,
    1. u must build the sql statement out of th exec
    2. all the sql statements that uses the @tablename variable must be dynamic.
    3. why cursors???

  5. #5
    Join Date
    Apr 2004
    Posts
    5
    Originally posted by yoavmaimon
    what u wrote wont work,
    1. u must build the sql statement out of th exec
    2. all the sql statements that uses the @tablename variable must be dynamic.
    3. why cursors???
    Dear Sir:
    Why cursor?
    Because I have to parse the records and and by the columns to do some extra action.
    After actions, and I will use Waitfor the pause the cursor engine to limit the resource of this stored procedure.
    Do you know how to set the priority of specified account in sql server,
    then I can avoid to use cursor.

    regards,

    Stanley Huang

Posting Permissions

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