SQL Server WHILE & Cursors Examples

---------------------------
-- 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