locked
While Loop not working RRS feed

  • Question

  • Hi Gurus'

    I have a while loop script not working for me. I am just a beginner and need a little help.

    what I want is update all first names to 'Robin'

    see the script for detail ....

    ----table holding first and last name
    create table MyTable (id int, FirstName varchar(200), LastName varchar(200))
    insert into MyTable values (1, 'Barack', 'Obama'), (2, 'Bill', 'Clinton'), (3, 'Robin', 'Williams')
    
    ----
    declare @table table (id int, name varchar(200), ColumnName varchar(200))
    insert into @table 
    select 1, 'FirstName'
    union 
    select 2, 'LastName'
    			
    
    			
     DECLARE @rowID int, @maxRowID int, @ColumnName varchar(200), @SQL varchar(max) 
     
     
     set @rowID = 1
     set @maxRowID = (SELECT max(id) FROM @table)
     
    
    		WHILE (@rowID <=@maxRowID)
    			BEGIN
    	
    				 SET @ColumnName = (SELECT ColumnName FROM @table WHERE ID = @rowID)
    				 
    				 set @SQL = '
    								update MyTable
    								set '+ @ColumnName +' = ''Robin''	 
    							'
    				exec sp_executesql @SQL
    
    				SET @rowID = @rowID + 1
    			END
    GO
    
    select * from mytable 
    drop table MyTable

    thanks in advance

    ebro

    Monday, August 25, 2014 2:36 PM

Answers

  • what are you trying to do?

    I just fix the error

    ---table holding first and last name
    create table MyTable (id int, FirstName varchar(200), LastName varchar(200))
    insert into MyTable values (1, 'Barack', 'Obama'), (2, 'Bill', 'Clinton'), (3, 'Robin', 'Williams')
    
    ----
    declare @table table (id int,  ColumnName varchar(200))
    insert into @table 
    select 1, 'FirstName'
    union 
    select 2, 'LastName'
    			
    --SELECT max(id) FROM @table
    			
     DECLARE @rowID int, @maxRowID int, @ColumnName varchar(200), @SQL nvarchar(max) 
     
     
     set @rowID = 1
     set @maxRowID = (SELECT max(id) FROM @table)
     
    
    		WHILE (@rowID <=@maxRowID)
    			BEGIN
    	
    				 SET @ColumnName = (SELECT ColumnName FROM @table WHERE ID = @rowID)
    				 
    				 set @SQL = '
    								update MyTable
    								set '+ @ColumnName +' = ''Robin''	 
    							'
    				print @SQL
    				exec sp_executesql @SQL
    
    				SET @rowID = @rowID + 1
    			END
    GO
    
    select * from mytable 
    drop table MyTable

    --Prashanth

    • Marked as answer by ebrolove Monday, August 25, 2014 3:12 PM
    Monday, August 25, 2014 2:43 PM

All replies

  • what are you trying to do?

    I just fix the error

    ---table holding first and last name
    create table MyTable (id int, FirstName varchar(200), LastName varchar(200))
    insert into MyTable values (1, 'Barack', 'Obama'), (2, 'Bill', 'Clinton'), (3, 'Robin', 'Williams')
    
    ----
    declare @table table (id int,  ColumnName varchar(200))
    insert into @table 
    select 1, 'FirstName'
    union 
    select 2, 'LastName'
    			
    --SELECT max(id) FROM @table
    			
     DECLARE @rowID int, @maxRowID int, @ColumnName varchar(200), @SQL nvarchar(max) 
     
     
     set @rowID = 1
     set @maxRowID = (SELECT max(id) FROM @table)
     
    
    		WHILE (@rowID <=@maxRowID)
    			BEGIN
    	
    				 SET @ColumnName = (SELECT ColumnName FROM @table WHERE ID = @rowID)
    				 
    				 set @SQL = '
    								update MyTable
    								set '+ @ColumnName +' = ''Robin''	 
    							'
    				print @SQL
    				exec sp_executesql @SQL
    
    				SET @rowID = @rowID + 1
    			END
    GO
    
    select * from mytable 
    drop table MyTable

    --Prashanth

    • Marked as answer by ebrolove Monday, August 25, 2014 3:12 PM
    Monday, August 25, 2014 2:43 PM
  • Why do you need a loop for this?

    As far as I see what you need is just this

    DECLARE @Sql varchar(5000)

    SELECT @Sql= STUFF( (SELECT CHAR(10) + CHAR(13) + 'UPDATE ' + TABLE_NAME + ' SET ' + COLUMN_NAME + ' = ''Robin'';' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('FirstName','LastName') FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,'') PRINT @Sql EXEC(@Sql)



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, August 25, 2014 2:54 PM
  • declare @table table (id int, name varchar(200), ColumnName varchar(200))

    insert into @table

    select 1, 'FirstName'

    union

    select 2, 'LastName'

    Problem is with your insert script in table variable. If you want to insert data only in 2 columns then specify the required column names in the insert script:

    declare @table table (id int, name varchar(200), ColumnName varchar(200)) 
    
    insert into @table (id, ColumnName)
    select 1, 'FirstName' 
    union 
    select 2, 'LastName' 

    OR remove the third column ("Name") from table variable. Also, you have to change the datatype of statement @SQL to NVARCHAR(MAX).

    With your script you will update the firstname and lastname to 'Robin'. I don't know why you want to do it but there is a simple way out:

    UPDATE #MyTable 
    SET FirstName = 'Robin',
    	LastName = 'Robin'


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".


    Monday, August 25, 2014 2:58 PM
  • I got what I wanted using your replay.... but what did you change from my script? what was the error? so I can learn....

    thanks man......


    ebro

    Monday, August 25, 2014 3:14 PM
  • Problem was with your insert script in table variable. If you want to insert data only in 2 columns then specify only the required column names in the insert script else remove the third column "Name".

    Also, you have to change the datatype of statement @SQL to NVARCHAR(MAX).


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Monday, August 25, 2014 3:18 PM
  • You can refer the Shailesh reply. 

    Your script has three columns for the @Table table data type but you are trying to insert only two columns.

    Also, you are trying to use sp_executesql which holds only Unicode string that contains in @SQL datatype hence I've changed it nvarchar from varchar.

    --Prashanth

    Monday, August 25, 2014 3:24 PM
  • Excellent!!

    Actually, the first part was an accidental error....I did not know about the Unicode requirement.... That part I learned.......

    And yes, you guys are great......thanks everybody for your swift response......

    thank you so much....


    ebro

    Monday, August 25, 2014 3:29 PM
  • Excellent!!

    Actually, the first part was an accidental error....I did not know about the Unicode requirement.... That part I learned.......

    And yes, you guys are great......thanks everybody for your swift response......

    thank you so much....


    ebro

    Did you see my suggestion at all?

    I still cant understand why you need the WHILE loop!


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, August 25, 2014 3:31 PM
  • I saw your suggestion brother. I feel your point on avoiding using while loop when a set based solution can solve problems. My case is requiring me to resort to While Loop and the solution was perfectly given by Prashanth.

    But I see the point you are making. Thank you so much sir....


    ebro

    Monday, August 25, 2014 7:23 PM