-----------------------------------------------
--- T-SQL - Variables, Conditions and Loops ---
-----------------------------------------------

-----------------
--- Variables ---
----------------- 

-- DECLARE EXAMPLE
------------------

DECLARE @first_var NUMERIC(3),
		@sec_var VARCHAR(20)

-- INITIALIZE EXAMPLE
---------------------

DECLARE @var1 NUMERIC(3), 
	    @var2 VARCHAR(20)

SET @var1 = 46
SET @var2 = 'Bingo!'

Print @var1
Select @var1

-- VARIABLE PRINT
-----------------

DECLARE
	@v_var1 int,
	@v_var2 varchar(20)
BEGIN
	SET @v_var1=1 
	SET @v_var2='Hello World'
	PRINT CAST(@v_var1 as varchar) + ' '+ @v_var2
END

DECLARE 
	@first_var NUMERIC(3), 
	@sec_var VARCHAR(20)

SET @first_var = 46
SET @sec_var = 'A Number'

PRINT @first_var
PRINT CAST(@first_var AS VARCHAR(20)) +' IS '+ @sec_var --ONLY CHARACTERS
PRINT 8

SELECT @first_var, @sec_var

-------------------------------------
--- Assigning Values to Variables ---
-------------------------------------

-- DECLARE---

DECLARE @Param3 INT = 100
DECLARE @Param4 VARCHAR(50) = 'John'

-- SELECT--

DECLARE @Param1 int

SELECT @Param1 = DepartmentID 
FROM HumanResources.Department 
WHERE NAME ='Engineering'

-- SET --

DECLARE @Param1 int

SET @Param1 = 7

-- SET FROM SELECT --

DECLARE @Param1 int

SET @Param1 = (SELECT DepartmentID  
               FROM HumanResources.Department 
	           WHERE NAME ='Engineering')

------------------------------------------
--- INTERACTION T-SQL AND SQL STATMENT ---
------------------------------------------

-- Code Block --

DECLARE 
		@prod_id NUMERIC(4), 
	    @prod_name VARCHAR(30), 
	    @price MONEY
BEGIN
	SET @prod_id = 1

	SELECT @prod_name = productname, @price = unitprice
	FROM products
	WHERE productid = @prod_id

	PRINT 'Name & Price For Product Number '+CAST(@prod_id AS VARCHAR)+' Are: '+@prod_name+' '+CAST(@price AS VARCHAR)
END

---------------------
-- CONDITIONS - IF --
---------------------

DECLARE @prod_id NUMERIC(4), 
		@prod_name VARCHAR(30), 
		@price MONEY

SET @prod_id = 3

	SELECT @prod_name = productname, @price = unitprice
	FROM products
	WHERE productid = @prod_id

print @price

IF @price > 10
	PRINT @prod_name+ ' '+ cast(@price as varchar) +' - Too expansive product for me...'

--------------------------------------------------------
DECLARE @prod_id NUMERIC(4), 
	    @prod_name VARCHAR(30), 
	    @price MONEY

SET @prod_id = 2

	SELECT @prod_name = productname, @price = unitprice
	FROM products
	WHERE productid = @prod_id

IF @price > 20
	PRINT @prod_name +' - Too expansive product for me...'
	--Print @price
ELSE
	PRINT @prod_name+' - Just the right price for me!'
	--Print @price

----------------------------------------
-- IF CONDITION INCLUDING BEGIN & END --
----------------------------------------

DECLARE @prod_id NUMERIC(4), 
	@prod_name VARCHAR(30), 
	@price MONEY

SET @prod_id = 7

	SELECT @prod_name = productname, @price = unitprice
	FROM products
	WHERE productid = @prod_id

IF @price > 20
	BEGIN
		PRINT 'Before adding 20% to the unit price, the product -  ' +@prod_name+' costs'+CAST(@price AS CHAR(8))	
		SET @price= @price*1.2 --adding 20% to the unit price
		PRINT 'After adding 20% to the unit price, the product -  ' +@prod_name+' costs'+CAST(@price AS CHAR(8))
	END
ELSE
	PRINT 'The product '+@prod_name+ 'is not for me'

-------------
-- ELSE IF --
-------------

DECLARE @prod_id NUMERIC(4), 
	    @prod_name VARCHAR(30), 
	    @price MONEY
		
SET @prod_id = 1 --7 --3

	SELECT @prod_name = productname, @price = unitprice
	FROM products
	WHERE productid = @prod_id

IF @price > 20
	Begin
		PRINT @prod_name+' - Too expansive product for me...'
		If @price > 21
		print 'Greater Than 21'
	end
ELSE IF @PRICE > 10
		PRINT @prod_name+' - I''ll Think About It'
ELSE 	
	PRINT @prod_name+' - Must Buy!'

Print @price

----------
-- GOTO --
----------

DECLARE @prod_id NUMERIC(4), 
		@prod_name VARCHAR(30), 
		@price MONEY
				
SET @prod_id = 3

	SELECT @prod_name = productname, @price = unitprice
	FROM products
	WHERE productid = @prod_id

	IF @price > 20
		BEGIN
			GOTO HIGH_OPERATIONS 
		END		
	ELSE IF @PRICE > 10
		BEGIN
			GOTO MEDIUM_OPERATIONS 	
		END
	ELSE 
		BEGIN
		GOTO LOW_OPERATIONS
		END

LOW_OPERATIONS: 
	Begin
	PRINT 'Must Buy!'
	GOTO NAME_AND_PRICE_PRINTING
	end
MEDIUM_OPERATIONS:
	PRINT 'I''ll Think About It' 
HIGH_OPERATIONS:
	PRINT 'Too expansive product for me'
NAME_AND_PRICE_PRINTING:
	PRINT @prod_name  
	PRINT @price

-------------------
-- CASE EXAMPLES --
-------------------

--SIMPLE

SELECT orderid, discount,
	CASE Discount
			WHEN 0 THEN 'No Discount'
			WHEN 0.05 THEN 'Small Discount'
		ELSE 'Fine Discount'
     END As [Discount properties]
FROM [Order Details]

--SEARCH CASE

SELECT CustomerID, requireddate, shippeddate,
	CASE 
		WHEN ShippedDate IS NULL  
			THEN 'Call for schedule shipping'
		WHEN DATEDIFF(dd,ShippedDate,RequiredDate) < 0 
			THEN 'Call and apologize'
 		ELSE 'Shipped O.K'
END AS 'Shipping Status'
FROM Orders 

-------------------------------------
-- Using the case in a t-sql block --
-------------------------------------

DECLARE @name  varchar(20),
		@len  int,
        @res varchar(70)

	SELECT @name = lastName FROM employees
	WHERE employeeID = 1

SET @len = LEN(@name)

SET @res =  CASE 
	    WHEN @len > 5 THEN @name + ': Your Name Length is Higher than 5'
	    ELSE @name + ': Your Name Length is Less Or Equal To 5'
END

PRINT @res

select * from employees

-------------
--- LOOPs ---
-------------

------------------
--- WHILE LOOP ---
------------------

---1A---

-- Will The loop Complete after 100 runs?

DECLARE @counter INT=0

WHILE @counter < 100 
	BEGIN
		Print @counter;
	END

---1B---

-- Will The loop Complete after 100 runs?

DECLARE @counter INT=0

WHILE @counter < 100
BEGIN
	Print @counter
	SET @counter = @counter + 1
END

---2---

select ascii('B')
select char(97)

DECLARE @counter INT=0,
	@v_string int = 97,
	@Param2 varchar(10) 

WHILE @counter < 100 -- The loop will Complete after 100 runs
BEGIN
	set @Param2 = (select char(@v_string))
	Print cast(@counter as varchar) + @Param2

	SET @counter = @counter+1

	If @v_string = 122
		SET @v_string = 97
	Else
		SET @v_string = @v_string + 1
END

-- Using the while loop 
-- Printing even number and odd number

---3---
Select 3%2

DECLARE @num int 
SET @num = 1

WHILE @num <= 10
	BEGIN
		IF @num % 2 = 0
			BEGIN
				PRINT cast(@num as varchar(20)) + ' Zugi' 
			END
		ELSE
			BEGIN 
				PRINT cast(@num as varchar(20)) + ' Not Zugi' 
		END
            SET	@num = @num + 1 
	END


--WHILE LOOP - Using BREAK / CONTINUE
-------------------------------------

-- using the while loop 

DECLARE @num int 
SET @num = 1

WHILE (@num <= 10)
	BEGIN
		PRINT @num 
			IF @num = 7
				BEGIN 
					PRINT '7 Is a bad luck number !' 
					BREAK 
				END 
			SET @num = @num + 1 
	END

-------------------------------------------------------
DECLARE @num int 
SET @num = 1

WHILE (@num <= 10)
	BEGIN
		PRINT @num 
			SET @num = @num + 1 
			IF @num < 9
			CONTINUE
			ELSE
					PRINT '	This number is higher than 7'  
	END

-- Selecting The 10 First Products
-----------------------------

DECLARE @counter NUMERIC(2)

SET @counter = 1

WHILE @counter <= 10
BEGIN
	SELECT productid, productname, unitprice
	FROM products
	WHERE productid = @counter

	SET @counter = @counter+1

END

-- Printing The 10 First Products - Using CONTINUE
----------------------------------------------

DECLARE @counter NUMERIC(2), 
	@v_prodid INT,
	@v_prodname VARCHAR(20), 
	@v_unitprice INT

SET @counter = 1

WHILE @counter <= 10
BEGIN
	Select @v_prodid = productid, 
	       @v_prodname = productname, 
	       @v_unitprice = unitprice
	FROM products
	WHERE productid = @counter
	
	PRINT '------------------------' 
	PRINT @v_prodid
	PRINT @v_prodname
	PRINT @v_unitprice
	
	SET @counter = @counter+1
	IF @v_unitprice < 20 
		CONTINUE
	ELSE 
	PRINT 'Price Higher Than 20'
END


-- Using the EXIST

IF EXISTS (SELECT * 
	      FROM Employees
	      WHERE EmployeeID = 9)
	BEGIN
		PRINT 'Employee Exists'
	END
ELSE
	BEGIN 
		PRINT 'Unkonwn Employee'
	END

**********************************************

--- Using the EXIST with a WHILE loop

DECLARE @empnum int
SET @empnum = 1

WHILE @empnum < 20
BEGIN 
	IF EXISTS (SELECT * 
	      FROM Employees
	      WHERE EmployeeID = @empnum)
		BEGIN
			PRINT 'Employee number : ' + cast(@empnum as varchar(20)) + ' Does exist'
		END
	ELSE
		BEGIN 
			PRINT 'Employee number : ' + cast(@empnum as varchar(20)) + ' Does not exist'
		END
SET @empnum = @empnum + 1  
END