Discuss "Looping through variables." in the SQL Development forum.

mehere · #1 (**permalink**) June 4th, 2008, 11:32 AM

I have a CURSOR (I know, shouldn’t use them, but I have no other way) and I have declared several variables like so:

Code:

Declare @strAttrib_1 varchar(10)
Declare @strAttrib_2 varchar(10)
Declare @strAttrib_3 varchar(10)

Declare @i int

Set @i = 0

Now what I was hoping to do was loop through those to see if they contain various values. Something like this:

Code:

WHILE @i < 4
BEGIN
            IF @strAttrib_+@i = ‘something’
            BEGIN
                        --do something here
            END
            IF @strAttrib+@i = ‘somethingelse’
            BEGIN
                        --do something else here
            END
            --ETC
END

Is there a way to evaluate @strAttrib_ with the value in @i to get the actual value from the variable that was declared?

jmurrayhead · #2 (**permalink**) June 4th, 2008, 11:46 AM

Hmm...not sure if that can be done. But I know in SQL Server 2005, you can use lists and arrays. Not sure if this could help you or not, but this link shows something that looks like what you're trying to do: http://www.sommarskog.se/arrays-in-sql-2005.html

Code:

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
  RETURN
END

mehere · #3 (**permalink**) June 4th, 2008, 12:11 PM

well ... to make a long story short ... those variables will represent columns in a table (15 total) that contain a value to determine particular attributes for a driver. i need to see what attributes that driver has and insert that info into another table that is being used for another program to do some load testing. other than running 15 queries and dozens of "IF" statements, i was trying to condense it. yes, i'm a programmer and i'm lazy.

and j ... thanks. i saw that link along with another one for SQL Server 2000, my problem though, is some attributes have dates associated with them, they're in another 15 fields, but not all. i thought about putting them both in an array, but then i wouldn't know which date went with which attribute from the array. and yes, i did try it. if a driver had 8 attributes but only 3 of them had dates i would get an attribure array of 8 values but only a date array of 3 ... kind of hard to match up.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Discuss "Looping through variables." in the SQL Development forum.

SQL Development - Structured Query Language (SQL) is the talk of databases. Discuss topics such as joins, triggers and other advanced SQL topics.