-----------------------------------------------
--- PROGRAMMING OBJECTS - STORED PROCEDURES ---
-----------------------------------------------

--------CREATING PROCEDURE----------------
CREATE PROCEDURE my_first_proc
AS
BEGIN 
		SELECT employeeid,lastname, birthdate
		FROM employees
		WHERE employeeid IN (1,3,5)
END 

------EXECUTE PROCEDURE-------------------

EXECUTE my_first_proc
--OR
EXEC Procedures
--OR
my_first_proc

------ALTER PROCEDURE----------------------

ALTER PROCEDURE my_first_proc
AS
BEGIN
		SELECT employeeid,lastname, birthdate, city
		FROM employees
		WHERE employeeid IN (1,3,5,7)
END

EXEC my_first_proc

------DROPPING PROCEDURE--------------------

DROP PROC my_first_proc

-----------------------------------------
--- PARAMETERIZED STORED PROCEDURES: ----
-----------------------------------------
--- 1. INPUT PARAMETERS
-----------------------------------------
CREATE PROCEDURE emp_details_pro
(@emp_id INT)
AS
BEGIN
	SELECT employeeid, lastname, birthdate, city, country
	FROM employees
	WHERE employeeid = @emp_id
END

-- Run Procedure With No Parameters:
EXEC emp_details_pro

-- Run Procedure And Provide Parameters:
EXEC emp_details_pro 5
--OR
EXEC emp_details_pro @emp_id = 3

------------------------------------
--- INPUT PARAMETERS & VARIABLES ---
------------------------------------

CREATE PROCEDURE prod_pro
(@prod_id INT)
AS
DECLARE 
	@prod_name VARCHAR(30), @price NUMERIC
BEGIN
	SELECT @prod_name = productname, @price = unitprice
	FROM products
	WHERE productid = @prod_id

	IF @price > 20
		PRINT 'Product: '+@prod_name+' Costs MORE than 20'
	ELSE
		PRINT 'Product: '+@prod_name+' Costs LESS than 20'
END

EXEC prod_pro 5

-----------------------------------------
--- PARAMETERIZED STORED PROCEDURES: ----
-----------------------------------------
--- 2. OUTPUT PARAMETERS
-----------------------------------------

CREATE PROCEDURE emp_output_pro
(@emp_id INT , 
@lname VARCHAR(30) OUTPUT,
@fname VARCHAR(30) OUTPUT)
AS
BEGIN
	SELECT @lname = lastname, @fname = firstname
	FROM employees
	WHERE employeeid= @emp_id
END


EXEC emp_output_pro @emp_id = 3 ---The Wrong way of exec, out parameters must be Declared

--- The Currect way

DECLARE @last_name VARCHAR(30), 
		@first_name VARCHAR(30)

EXEC emp_output_pro 3, @last_name OUTPUT , @first_name OUTPUT

PRINT 'The full name of employee 3 Is: ' +@last_name +' '+ @first_name

-------------------------------
--- DML & STORED PROCEDURES ---
-------------------------------

CREATE PROCEDURE new_cust_pro
 @custid CHAR(5),
 @compname VARCHAR(20),
 @contname VARCHAR(20),
 @conttitle VARCHAR(30),
 @address VARCHAR(60),
 @city VARCHAR(15),
 @region VARCHAR(15),
 @postalcode VARCHAR(10),
 @country VARCHAR(15),
 @phone VARCHAR(24),
 @fax VARCHAR(24)
AS
BEGIN
	INSERT INTO customers
	VALUES(@custid,@compname,@contname,@conttitle,@address,@city,@region,@postalcode,@country,@phone,@fax)
END

BEGIN TRAN

EXEC new_cust_pro 'TST', 'Such a nice company', 'Thomas Hardy', 'Owner', 'Yad-Natan 256', 
                   NULL, 'LACHISH', 79352, 'Israel', '000-000-000', NULL

SELECT * FROM Customers
WHERE CustomerID = 'TST'

ROLLBACK

SELECT * FROM Customers
WHERE CustomerID = 'TST'

-----RETURN VALUE

ALTER PROCEDURE new_cust_pro
 @custid CHAR(5),
 @compname VARCHAR(20),
 @contname VARCHAR(20),
 @conttitle VARCHAR(30),
 @address VARCHAR(60),
 @city VARCHAR(15),
 @region VARCHAR(15),
 @postalcode VARCHAR(10),
 @country VARCHAR(15),
 @phone VARCHAR(24),
 @fax VARCHAR(24)
AS
BEGIN
	IF @country IS NULL
			BEGIN
				PRINT 1
				RETURN -1
			END
	ELSE IF @country = 'Israel'
			BEGIN
				INSERT INTO customers
				VALUES(@custid,@compname,@contname,@conttitle,@address,
						@city,@region,@postalcode,@country,@phone,@fax)
				PRINT 2
				RETURN 2
			END
		ELSE 
			BEGIN
				INSERT INTO customers
				VALUES(@custid,@compname,@contname,@conttitle,@address,
						@city,@region,@postalcode,@country,@phone,@fax)
				PRINT 0
				RETURN 0
			END
END
---- EXECUTING PROCEDURE & CHECKING THE RETURN VALUE

BEGIN TRAN

DECLARE @result INT --- Declaring the variable for the return value

EXEC @result = new_cust_pro 'MSM', 'Such a nice company', 'Thomas Hardy', 'Owner', 'Yad-Natan 256',
							 NULL, 'LACHISH', 79352, 'Israel', '000-000-000', NULL
	IF @result = -1
		PRINT 'No value for country. Sorry, can not add customer'
	ELSE IF @result = 2
		PRINT 'You just insert value of the best country of all'
			ELSE
				PRINT 'Customer addition executed.'

SELECT * FROM customers
order by customerid

ROLLBACK