CIS 310 SQL Problems 14 Stored Procedure Queries.sql

-- Rob DeVoto
--CIS 310
-- Fall 2022
-- Problems 14 - Stored Procedure Queries
-- Uses Staywell_DB_Script

-- DO NOT NEED TO END STORED PROCEDURE CODE WITH A ; LIKE OTHER CODE

-- CREATE A STORED PROCEDURE
CREATE PROCEDURE REPS_CUSTOMER_LIST @REP_ID CHAR(2) = NULL
AS
BEGIN
	SELECT CUST_ID, LAST_NAME+', '+FIRST_NAME AS CUST_NAME  -- CAN ALSO USE CONCAT( , , )
	FROM CUSTOMER
	WHERE REP_ID = @REP_ID
END

-- RUN THE STORED PROCEDURE
EXEC REPS_CUSTOMER_LIST'10'
EXEC REPS_CUSTOMER_LIST

-- ALTER THE STORED PROCEDURE, MAKE IT BETTER TO ACCOUNT FOR NULL VALUES
ALTER PROCEDURE REPS_CUSTOMER_LIST @REP_ID CHAR(2) = NULL
AS
BEGIN
	IF @REP_ID IS NULL
	BEGIN
		PRINT 'REP ID not provided'
	END
	ELSE
		SELECT CUST_ID, LAST_NAME+', '+FIRST_NAME AS CUST_NAME  -- CAN ALSO USE CONCAT( , , )
		FROM CUSTOMER
		WHERE REP_ID = @REP_ID
END

-- RUN THE STORED PROCEDURE
EXEC REPS_CUSTOMER_LIST'10'
EXEC REPS_CUSTOMER_LIST

-- UPDATE A STORED PROCEDURE
CREATE PROCEDURE CHG_CUST_LAST_NAME
@CUST_ID CHAR(3),
@NEWLASTNAME VARCHAR(20)
AS
BEGIN
	UPDATE CUSTOMER
	SET LAST_NAME = @NEWLASTNAME
	WHERE CUST_ID = @CUST_ID
END

-- CHECK AND RUN THE UPDATED STORED PROCEDURE, SEE CHANGES
SELECT * FROM CUSTOMER
EXEC CHG_CUST_LAST_NAME '721', 'Jackson'
SELECT * FROM CUSTOMER

-- DELETE STORED PROCEDURE - DELETES A RECORD, NOT A WHOLE PROCEDURE
CREATE PROCEDURE DEL_INVOICE
@INVOICE_NUM CHAR(5)
AS
BEGIN
	DELETE
	FROM INVOICE_LINE
	WHERE INVOICE_NUM = @INVOICE_NUM  --FOREIGN KEY IN IVOICE_LINE TABLE, SO MUST DELETE FROM HERE FIRST DUE TO REFERENTIAL INTEGRITY
	DELETE
	FROM INVOICES
	WHERE INVOICE_NUM = @INVOICE_NUM  --PRIMARY KEY IN IVOICE_LINE TABLE
END

-- RUN THE NEW DELETE STORED PROCEDURE
SELECT * FROM INVOICES I INNER JOIN INVOICE_LINE IL ON I.INVOICE_NUM = IL.INVOICE_NUM;
EXEC DEL_INVOICE '14216'
SELECT * FROM INVOICES I INNER JOIN INVOICE_LINE IL ON I.INVOICE_NUM = IL.INVOICE_NUM;