fbpx

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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…