locked
examples for do while coniditions in sql server RRS feed

  • Question

  • User-1722422105 posted

    hi all

    give some scenarios for  do while conditions in sql server while writing in the stored procedure.

    thanks and regards

    siddu

    Thursday, August 16, 2018 11:42 AM

All replies

  • User632428103 posted

    Hello,

    here is it a little SAMPLE where i have two temp table with some bad data

    i make a WHILE on a first table and copy data if not already on the second table

    it's just a sample, explain perhpas your requierement and some people can help you more ...

    copy my code in SSMS and press F5 :)

    declare @tbl table(id int, dt datetime, completeName varchar(100), updated int)
    INSERT INTO @tbl values(15, '20151130', 'Me',0), (125,'20151110', 'other people',0), (10,'20151201', 'candidat 1',0), (7,'20151201', 'candidat 2',0)
    SELECT * FROM @tbl order by dt, completeName 
    --
    declare @tblResult table(dt datetime, completeName varchar(80))  
    
    declare @cnt int = 0
    declare @dateCompare datetime
    declare @name varchar(100)
    declare @id int 
    
    -- IF OTHER CODE ADD = to the while
     WHILE(@cnt < (SELECT COUNT(*) FROM @tbl))
     BEGIN
     
     -- display counter testing
     --SELECT @cnt
     
     SELECT top 1 @id = ID, @dateCompare=dt, @name=completeName FROM @tbl where updated = 0 order by dt, completeName
    	
    	-- if date is alredy present insert null
    	IF ( (SELECT count(dt) FROM @tblResult where dt = @dateCompare) > 0)
    	BEGIN
    		INSERT INTO @tblResult(dt, completeName)
    		SELECT null, @name
    	END
    	ELSE
    	BEGIN
    		-- date present insert date
    		INSERT INTO @tblResult(dt, completeName)
    		SELECT @dateCompare,@name 
    	END
    	
             -- update field updated with value 1	
    	UPDATE @tbl set updated = 1 where id= (SELECT top 1 ID FROM @tbl where updated = 0 order by dt, completeName)
    	SET @cnt = @cnt +1
    
     END
     
     -- display result
     SELECT CONVERT(varchar(10), dt, 103) as Date, completeName as candidate FROM @tblResult
     
     -- display first table => updated
     SELECT * FROM @tbl

    Thursday, August 16, 2018 12:57 PM
  • User-893317190 posted

    Hi sidu,

    You could use do while conditions to populate data of  an empty table.

     Below  I use do while to populate data of a temp table with  continuous dates in 2018.

    What’s the use of  this table?

    For example , I have a  table which records  the employees attendance.

    I could get the absence dates of one employee through this table.

    Below is the sql.

    declare @count as date='20180101'
    
    
    declare @end as date= '20181231'
    
    declare @temp table(dateCol date);
    
    while @count<=@end
    begin
    
    insert into @temp  values(@count)
    
    set @count=DateAdd(day,1,@count)
    end
    
    select * from  @temp
    select count(*) as totalDate from @temp
    
    
    

    The result.

    Best regards ,

    Ackerly Xu

    Monday, August 20, 2018 1:34 AM