locked
Looping Through A Table RRS feed

  • Question

  • User1231829591 posted

    Hi all,

    I'm trying to loop through a temp table going from the first column to the last column and getting the data I need using a while loop.

    My table has the following schema and each column will only have one row max.

    #myTable(RowID int not null identity(1,1) primary Key, Num1 int, Num2 int, Num3 int, Num4 int)

    I have tried to loop through my table using the following SQL code

    create proc spMyProc
    @myVal1 int,
    @myVal2 int,
    @myVal3 int,
    @myVal4 int
    as
    begin
    
    declare @currentCol int    -- current Column name
    declare @currentColVal int -- current Column Value
    declare @counter int
    
    create #myTable(RowID int not null identity(1,1) primary Key, Num1 int, Num2 int, Num3 int, Num4 int)
    insert #myTable(Num1, Num2, Num3, Num4) Values(@myVal1, @myVal2, @myVal3, @myVal4)
    set @counter = 1
    while @counter < 4 begin set @currentCol = 'Num' + @counter -- Get name of current column set @currentColVal = Select @currentCol from #myTable -- Do stuff -- Do stuff -- Do stuff set @counter= @counter + 1 end

    -- Do stuff
    end

    I get the error message 

    Conversion failed when converting the varchar value 'Num' to data type int.

    How do I parse my query so that the my variables are seen as numbers and not text? Thanks in advance for your help.

    Friday, October 23, 2015 9:05 AM

Answers

  • User77042963 posted

    You need to think about Set-based solution what you are working T-SQL. You can solve a problem with a loop but it is not an optimum solution in most cases. You can provide with your table DDL and some sample data with your expected result, so we may have a better solution for you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 23, 2015 11:05 AM
  • User1644755831 posted

    Hello ManyTitles,

    You can use sys.tables and and sys.columns to create a dyanamic sql and do this.

    Please see: http://stackoverflow.com/questions/20463365/looping-through-column-names-with-dynamic-sql

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 25, 2015 10:52 PM

All replies

  • User2103319870 posted

    ManyTitles

    Conversion failed when converting the varchar value 'Num' to data type int.

    You are trying to concatenate a varchar value 'Num' with and integer value '@counter'. Try converting the value to Varchar prior to concatenating like below

    set @currentCol = 'Num' + CAST(@counter AS VARCHAR(20))

    Complete Code

    create proc spMyProc
    @myVal1 int,
    @myVal2 int,
    @myVal3 int,
    @myVal4 int
    as
    begin
    
    declare @currentCol int    -- current Column name
    declare @currentColVal int -- current Column Value
    declare @counter int
    
    create #myTable(RowID int not null identity(1,1) primary Key, Num1 int, Num2 int, Num3 int, Num4 int)
    insert #myTable(Num1, Num2, Num3, Num4) Values(@myVal1, @myVal2, @myVal3, @myVal4)
    
    while @counter < 4
    begin 
    --Convert the @counter variable to varchar prior to concatenating
     set @currentCol = 'Num' + CAST(@counter AS VARCHAR(20))   -- Get name of current column 
     set @currentColVal = Select @currentCol from #myTable
     -- Do stuff
     -- Do stuff
     -- Do stuff
     set @counter= @counter+ 1
    end 
    
    -- Do stuff
    
    end

    Friday, October 23, 2015 9:21 AM
  • User1231829591 posted

    Hi, thanks for replying. I am still getting an error using your suggestion. 

    Conversion failed when converting the nvarchar value 'Num1' to data type int.

    I tried declaring the variable @currentCol as nvarchar(20) but I get the same exact error as before.

    Friday, October 23, 2015 10:02 AM
  • User2103319870 posted

    ManyTitles

    I tried declaring the variable @currentCol as nvarchar(20) but I get the same exact error as before.

    You need to Declare @currentColVal variable also to Varchar datatype.

    Try with the below code

    create proc spMyProc
    @myVal1 int,
    @myVal2 int,
    @myVal3 int,
    @myVal4 int
    as
    begin
    --Change the DataType of below two variables
    declare @currentCol VarChar(20)    -- current Column name
    declare @currentColVal VarChar(20) -- current Column Value
    declare @counter int
    
    create table #myTable(RowID int not null identity(1,1) primary Key, Num1 int, Num2 int, Num3 int, Num4 int)
    --insert #myTable(Num1, Num2, Num3, Num4) Values(@myVal1, @myVal2, @myVal3, @myVal4)
    insert #myTable(Num1, Num2, Num3, Num4) Values(1, 2, 3, 4)
    Set @counter = 0;
    while @counter < 4
    begin 
    --Convert the @counter variable to varchar prior to concatenating
     set @currentCol = 'Num' + CAST(@counter AS VARCHAR(20))   -- Get name of current column 
     Select @currentColVal =  @currentCol from #myTable
     -- Do stuff
     -- Do stuff
     -- Do stuff
     set @counter= @counter+ 1
    end 
    
    -- Do stuff
    
    end
    

    Friday, October 23, 2015 10:32 AM
  • User1231829591 posted

    Thanks for your help. I tried what you've suggested and although it no longer throws an error,  it shows 0 instead of the correct number when I print out the value of @currentColVal. 

    Friday, October 23, 2015 10:48 AM
  • User77042963 posted

    You need to think about Set-based solution what you are working T-SQL. You can solve a problem with a loop but it is not an optimum solution in most cases. You can provide with your table DDL and some sample data with your expected result, so we may have a better solution for you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 23, 2015 11:05 AM
  • User1231829591 posted

    Hi limno, thanks for replying. I thought about solving my problem using set-based approach all day but I couldn't come up with a way to write queries that rely strictly on SQL.

    I need my queries to be flexible; they need to be able to dynamically manipulate data, add, delete, or drop columns. The keyword is dynamic and I was not able to write such dynamic queries without using any case statements, if statements, and loops. The code I posted above was just a simple example of what I am trying to do.

    I've been reading up on set-based approach and quite honestly the examples I've seen made me come to the conclusion that set-based approach cannot be used for what I am trying to achieve.

    Saturday, October 24, 2015 3:05 AM
  • User1644755831 posted

    Hello ManyTitles,

    You can use sys.tables and and sys.columns to create a dyanamic sql and do this.

    Please see: http://stackoverflow.com/questions/20463365/looping-through-column-names-with-dynamic-sql

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 25, 2015 10:52 PM