Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    Kentucky
    Posts
    1

    Question Unanswered: Select data from #temp table in SQL

    I am building a dynamic query stored procedure. I am first filling a temp table with data:

    Declare
    @Counter int

    drop table #tempmerge
    create table #tempmerge(IDIndex int IDENTITY, CitationNum char(9),Exp1 int)

    insert into #tempmerge
    Select E_Cit_For_Merge, Count(*) as Exp1
    from dbo.E_Citation_XML_Data
    group by E_Cit_For_Merge
    having Count(*)>1
    select * from #tempmerge

    Results returned from #tempmerge table:

    IDIndex CitationNum Exp1
    ----------- ----------- -----------
    1 4AA020621 2
    2 4AA022361 2
    3 4AA022391 2
    4 4AA022423 2
    5 4AA022532 3
    6 4AA027761 2
    7 4AA030513 2

    Then, I want to use a while loop, looping thru the #tempmerge table
    and retrieving the CitationNum value of each row:

    set @RowCount = (Select Count(*) from #tempmerge)
    set @Counter = 1
    While @Counter <= @RowCount
    Begin

    Set @WhereStatement2 = ' where E_Cit_For_Merge= (Select CitationNum from #tempmerge
    where IDIndex = @Counter)'

    E_Cit_For_Merge is a field in a SQL table.
    I Declare @Counter as int.

    I get the Error message that:

    FROM E_Citation_XML_Data where E_Cit_For_Merge= (Select CitationNum from #tempmerge
    where IDIndex = @Counter)

    Server: Msg 137, Level 15, State 2, Line 24
    Must declare the variable '@Counter'.

    Any Suggestions?
    Thanks
    JEB

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In SQL, @ variables only exist within the batch where they are declared. You need to declare @counter again in your second batch.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    set @RowCount = (Select Count(*) from #tempmerge)
    set @Counter = 1
    While @Counter <= @RowCount
    Begin

    Set @WhereStatement2 = ' where E_Cit_For_Merge= (Select CitationNum from #tempmerge
    where IDIndex =' + @Counter +')'
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by Enigma
    set @RowCount = (Select Count(*) from #tempmerge)
    set @Counter = 1
    While @Counter <= @RowCount
    Begin

    Set @WhereStatement2 = ' where E_Cit_For_Merge= (Select CitationNum from #tempmerge
    where IDIndex =' + @Counter +')'
    The last WHERE clause should read:

    where IDIndex = ' + cast(@Counter as varchar(25)) + ')'

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Why the varchar(25) ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why varchar? Because @Counter is an integer. Why 25? Don't know, just picked a number

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    My bad !!!

    Varchar(11) would be enough for an integer
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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