--------------------------- -- WHILE LOOP --------------------------- -- Ex1 DECLARE @v_var int BEGIN SET @v_var = 0 WHILE @v_var < 10 BEGIN PRINT @v_var SET @v_var = @v_var + 1 END END -- Ex2 SELECT * FROM employees DECLARE @v_employees_counter int , @v_lastname varchar(25) BEGIN SET @v_employees_counter = 1 WHILE @v_employees_counter <= 9 BEGIN SELECT @v_lastname = lastName FROM employees WHERE employeeID = @v_employees_counter PRINT ' ----------------------' PRINT @v_lastname IF LEN(@v_lastname) > 6 PRINT 'Your name is toooooo long !' ELSE PRINT 'Your name is tooo short !' PRINT ' ----------------------' SET @v_employees_counter = @v_employees_counter + 1 END END -- P2 DECLARE @v_productID int , @v_productName varchar(25) , @v_unitPrice int BEGIN SET @v_productID = 1 WHILE @v_productID < 10 BEGIN SELECT @v_productName = productName , @v_unitPrice = unitPrice FROM products WHERE productID = @v_productID PRINT @v_productName PRINT @v_unitPrice IF @v_unitPrice < 50 PRINT ' < 50' ELSE PRINT ' > 50' PRINT '-----------------' SET @v_productID = @v_productID + 1 END END SELECT * FROM suppliers -- P3 DECLARE @v_supplierID int , @v_companyName varchar(25) , @v_city varchar(25) BEGIN SET @v_supplierID = 1 WHILE @v_supplierID <= 10 BEGIN SELECT @v_companyName = companyName ,@v_city = city FROM suppliers WHERE supplierID = @v_supplierID IF @v_city = 'London' PRINT @v_companyName + ' - ' + @v_city --IF @v_city = 'London' PRINT 'yes !' ELSE PRINT 'no !' SET @v_supplierID = @v_supplierID + 1 END END ----------------------------- -- CURSORS ---------------------------- GO DECLARE @v_productName varchar(25) DECLARE myCursor CURSOR FOR SELECT productName FROM products WHERE categoryID = 5 BEGIN OPEN myCursor FETCH NEXT FROM myCursor INTO @v_productName WHILE @@FETCH_STATUS = 0 BEGIN PRINT @v_productName FETCH NEXT FROM myCursor INTO @v_productName END CLOSE myCursor DEALLOCATE myCursor END -- P4 SELECT * FROM employees GO DECLARE @v_firstName varchar(25) , @v_lastName varchar(25) DECLARE myCursor CURSOR FOR SELECT firstName , lastName FROM employees WHERE city = 'London' ORDER BY firstName BEGIN OPEN myCursor FETCH NEXT FROM myCursor INTO @v_firstName , @v_lastName WHILE @@FETCH_STATUS = 0 BEGIN PRINT @v_firstName + ' ' + @v_lastName FETCH NEXT FROM myCursor INTO @v_firstName , @v_lastName END CLOSE myCursor DEALLOCATE myCursor END -- P3 DECLARE @v_productName varchar(25), @v_unitPrice int , @v_productID int , @v_avg int DECLARE myCursor CURSOR FOR SELECT productName , unitPrice , productID FROM products WHERE categoryID = 5 BEGIN SELECT @v_avg = AVG(unitPrice) FROM products WHERE categoryID = 5 OPEN myCursor FETCH NEXT FROM myCursor INTO @v_productName , @v_unitPrice , @v_productID WHILE @@FETCH_STATUS = 0 BEGIN PRINT @v_productName + ' ' + CAST(@v_unitPrice AS VARCHAR) IF @v_unitPrice > @v_avg BEGIN PRINT '@v_unitPrice > @v_avg' UPDATE products SET unitPrice = unitPrice * 1.1 WHERE productID = @v_productID END ELSE IF @v_unitPrice < @v_avg BEGIN PRINT '@v_unitPrice < @v_avg' UPDATE products SET unitPrice = unitPrice * 0.9 WHERE productID = @v_productID END FETCH NEXT FROM myCursor INTO @v_productName , @v_unitPrice , @v_productID END CLOSE myCursor DEALLOCATE myCursor END