Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Question Unanswered: Cursor/variable help

    I'm not sure about this one so if someone could help I'd appreciate this.

    As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a)
    and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.
    Attached Thumbnails Attached Thumbnails cusror1.jpg  

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, you can't use a character variable to specify an object name in most Transact-SQL statements. Depending on a number of factors outside the immediate discussion, you may be able to use dynamic SQL to replace:
    Code:
    SELECT * FROM @name1
    using syntax like:
    Code:
    EXECUTE ('SELECT * FROM [' + @name1 + ']')
    There are a number of possible pitfalls to this approach, but give it a shot and see if it works for you.

    -PatP

  3. #3
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    Pat, thanks for your help. A modified version of the query statement worked...

    EXECUTE ('SELECT * FROM [' + @name1 + '].dbo.sysfiles')

    It produced the results I desired.

    Could you recommend a good TSQL book to use for reference?

    I don't do much of the way of indepth SQL work, but would be nice to have a good reference. Thanks.

Posting Permissions

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