Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Question Unanswered: Where is my Syntax off?

    Here is the error that I am getting:
    Msg 137, Level 15, State 2, Line 16
    Must declare the scalar variable "@animal_colors".
    Msg 137, Level 15, State 2, Line 22
    Must declare the scalar variable "@animal_colors".

    Here is my SQL Statement:
    go
    declare @animal_colors table (color varchar(50));
    declare @i int;
    declare @row_count int;

    INSERT @animal_colors select distinct description from color

    set @i = 0
    set @row_count = (select count(color) from @animal_colors)

    while @i < @row_count
    begin
    update animal
    set color =
    (select top 1 color
    from color
    where color.description = @animal_colors.color
    )
    from color
    inner join animal on animal.color = color.color
    where color.description = @animal_colors.color[@i]
    delete from color where color not in (select top 1 color from color
    where description = @animal_colors.color[@i]
    and description = @animal_colors.color[@i]
    set @i = @i +1
    end

    I'm trying to learn how to write these, and I think I'm pretty close, but can't quite figure out what I am missing.

    Thanks
    Shawn

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    The variable @anymal_colors is declared as a table, treat it as one. In otherwords, when you need information from the table, select from it. Keep in mind, there is no such thing as an array in SQL Server.

  3. #3
    Join Date
    Mar 2007
    Posts
    2
    Quote Originally Posted by roac
    The variable @anymal_colors is declared as a table, treat it as one. In otherwords, when you need information from the table, select from it. Keep in mind, there is no such thing as an array in SQL Server.
    Here is my latest Syntax, however I'm still getting errors when executing the loop. (Must declare variable animal_colors)

    go
    declare @animal_colors table (row_id int NOT NULL IDENTITY(1,1) ,color varchar(50))
    declare @i int
    declare @row_count int

    INSERT @animal_colors select distinct description from color

    set @i = 1
    set @row_count = (select max(row_id) from @animal_colors)

    while @i < @row_count
    begin
    update animal
    set color =
    (select top 1 color
    from color
    where color.description = @animal_colors.color
    and @animal.rowid = @i
    )
    from color
    inner join animal on animal.color = color.color
    where color.description = @animal_colors.color and @animal.rowid = @i
    delete from color where color not in (select top 1 color from color
    where description = @animal_colors.color and @animal.rowid = @i)
    and description = @animal_colors.color and @animal.rowid = @i
    @i = @i + 1
    end
    Last edited by SPetty1979; 03-17-07 at 11:14.

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    As raoc said, treat it like the table it is.
    Your
    Code:
    where color.description = @animal_colors.color 
    and @animal.rowid = @i
    should probably read
    Code:
    where color.description = (select color from @animal_colors where @animal_colors.rowid = @i)
    Same thing in the other where clause and the delete statements.

Posting Permissions

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