Professional Web Applications Themes

Help, variable in FROM clause keeps dropping out of scope - Microsoft SQL / MS SQL Server

Thanks in advance anyone who responds. I'm trying to use a variable within a sql statement. The variable works everywhere but in the FROM clause? Here is the pertinent pieces of my statement: DECLARE MyCounter INT SET MyCounter = 1 declare chapterstr varchar (50) WHILE (MyCounter < 10) SET ChapterStr = ('WishData_00'+ltrim(str (Mycounter)+'.dbo.tbluser')) BEGIN select userid, password, staffid from chapterstr SET MyCounter = MyCounter + 1 END...

Sponsored Links
  1. #1

    Default Help, variable in FROM clause keeps dropping out of scope


    Thanks in advance anyone who responds. I'm trying to use a
    variable within a sql statement. The variable works
    everywhere but in the FROM clause?

    Here is the pertinent pieces of my statement:

    DECLARE MyCounter INT
    SET MyCounter = 1
    declare chapterstr varchar (50)

    WHILE (MyCounter < 10)

    SET ChapterStr = ('WishData_00'+ltrim(str
    (Mycounter)+'.dbo.tbluser'))

    BEGIN

    select userid, password, staffid from
    chapterstr

    SET MyCounter = MyCounter + 1

    END

    Sponsored Links
    jeff Guest

  2. #2

    Default Re: Help, variable in FROM clause keeps dropping out of scope

    Jeff,

    If thats the requirement, you may need to use dynamic sql.Not a indication
    of good design.Refer the below article by Erland Sommarskog for more
    details..

    The Curse and Blessings of Dynamic SQL
    http://www.algonet.se/~sommar/dynamic_sql.html#Dyn_table


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "jeff" <org> wrote in message
    news:011901c355ec$f2f54930$gbl... 


    Dinesh.T.K Guest

  3. #3

    Default Re: Help, variable in FROM clause keeps dropping out of scope

    EXEC('select userid, password, staffid from ' + chapterstr)

    http://www.algonet.se/~sommar/dynamic_sql.html


    Aaron Guest

Similar Threads

  1. PHP variable scope bug in CT
    By lemp555 in forum Macromedia Contribute General Discussion
    Replies: 3
    Last Post: August 18th, 05:45 PM
  2. where clause as variable string
    By RichardG in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 28th, 09:54 PM
  3. Variable scope
    By Vincent V in forum ASP.NET General
    Replies: 10
    Last Post: August 21st, 12:53 PM
  4. Using variable in From clause
    By Glenn Stein in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 3rd, 08:45 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139