-- EXAMPLE 1: REPORTS ERROR DECLARE @VAR1 INT -- THIS IS A VARIABLE. USED TO STORE ANY DATA TEMPORARILY IN MEMORY. SET @VAR1 = 'ABC' SELECT @VAR1

-- EXAMPLE 2: REPORTS MESSAGE BEGIN TRY -- IF THERE IS ANY ERROR INSIDE THE TRY BLOCK THEN CATCH BLOCK IS AUTO EXECUTED. DECLARE @VAR1 INT SET @VAR1 = 'ABC' SELECT @VAR1 END TRY BEGIN CATCH PRINT 'ERROR DURING SQL BATCH EXECUTION' END CATCH

-- EXAMPLE 3: REPORTS ERROR + REPORTS MESSAGE BEGIN TRY DECLARE @VAR1 INT SET @VAR1 = 'ABC' SELECT @VAR1 END TRY BEGIN CATCH PRINT 'ERROR DURING SQL BATCH EXECUTION' ;THROW -- THIS STATEMENT REPORTS THE ACTUAL ERROR END CATCH

--

-- REQ: ASSUME YOU HAVE 1m ROWS IN THE A TABLE. HOW TO COPY DATA FROM THIS TABLE TO ANOTHER TABLE. -- CONDITION: YOU NEED TO EITHER COMPLETELY LOAD THE DATA OR NOTHING OR ALL.

USE TEMPDB GO

CREATE TABLE RESERVATION1 ( CRAFT_CODE VARCHAR(30), NO_OF_SEATS INT, CLASS VARCHAR(30) )

INSERT INTO RESERVATION1 VALUES ('AI01', 11, 'EC'), ('AI02', 12, 'EC'), ('AI03', 13, 'EC'), ('AI04', 14, 'EC') SELECT * FROM RESERVATION1

CREATE TABLE RESERVATION2 ( CRAFT_CODE VARCHAR(30), NO_OF_SEATS INT, CLASS VARCHAR(30) )

-- REQ: ASSUME YOU HAVE 1m ROWS IN THE A TABLE. HOW TO COPY DATA FROM THIS TABLE TO ANOTHER TABLE. -- CONDITION: YOU NEED TO EITHER COMPLETELY LOAD THE DATA OR NOTHING OR ALL.

CREATE TYPE TabDataType AS TABLE ( CRAFT_CODE VARCHAR(30), NO_OF_SEATS INT, CLASS CHAR(30) )

CREATE PROC USP_COPY_DATA ( @TVP TabDataType READONLY) -- @TVP IS A PARAMETER. AN UNKNOWN INPUT VALUE. TABLE VALUED PARAMETER AS BEGIN TRANSACTION BEGIN TRY INSERT INTO RESERVATION2 SELECT * FROM @TVP COMMIT TRANSACTION END TRY BEGIN CATCH PRINT 'ERROR DURING DATA COPY.' ROLLBACK END CATCH

DECLARE @TABVAR TabDataType INSERT INTO @TABVAR SELECT * FROM RESERVATION1 EXECUTE USP_COPY_DATA @TABVAR

SELECT * FROM RESERVATION2

-- EXAMPLE #2: OUTPUT PARAMETERS

-- OUTPUT PARAMETERS: ALSO CALLED "OUT" PARAMETERS. -- SUCH PARAMETERS THAT RETURN ONE OR MORE VALUES "FROM" THE GIVEN STORED PROCEDURE.

-- REQUIREMENT 1: HOW TO REPORT LIST OF ALL RESERVATIONS FOR SEATS ABOVE 10? SELECT * FROM RESERVATION1 WHERE NO_OF_SEATS > 10

-- REQUIREMENT 2: HOW TO REPORT LIST OF ALL RESERVATIONS FOR SEATS ABOVE A GIVEN VALUE? CREATE PROCEDURE USP_REPORT_RSV (@SEATS INT) AS SELECT * FROM RESERVATION1 WHERE NO_OF_SEATS > @SEATS

EXEC USP_REPORT_RSV 10

-- REQUIREMENT 3: HOW TO REPORT THE COUNT OF RESERVATIONS FOR SEATS ABOVE A GIVEN VALUE? ALTER PROCEDURE USP_REPORT_RSV (@SEATS INT, @COUNT INT OUT) AS SELECT @COUNT = COUNT(*) FROM RESERVATION1 WHERE NO_OF_SEATS > @SEATS

-- HOW TO EXECUTE ABOVE STORED PROCEDURE ? DECLARE @C INT EXEC USP_REPORT_RSV 10, @C OUT SELECT @C

-- HOW TO EXECUTE ABOVE STORED PROCEDURE ? DECLARE @COUNT INT EXECUTE USP_REPORT_RSV 10, @COUNT OUT SELECT 'THERE ARE ' + CAST (@COUNT AS VARCHAR(20)) + ' RESERVATIONS IN THE TABLE' SELECT 'THERE ARE ' + CONVERT(VARCHAR(20), @COUNT) + ' RESERVATIONS IN THE TABLE'

-- TASK 1: WRITE A PROCEDURE TO REPORT DATA (using above table) IN BELOW FORMAT BASED ON INPUT VALUES? -- THERE ARE n RESERVATIONS IN THE TABLE WITH NUMBER OF SEATS ABOVE m -- EXAMPLE OUTPUT: -- THERE ARE 4 RESERVATIONS WITH NUMBER OF SEATS ABOVE 1 -- THERE ARE 7 RESERVATIONS WITH NUMBER OF SEATS ABOVE 2

-- TASK 2: WRITE A PROCEDURE TO ACCEPT TABLENAME AS PARAMETER AND REPORT LIST OF CONSTRAINTS IN THAT TABLE ?

-- TASK 3: WRITE A PROCEDURE TO IDENTIFY THE LIST OF DUPLICATE ROWS IN A TABLE? CLUE: GROUP BY CHAPTER

-- TASK 4: WRITE A PROCEDURE TO REMOVE THE DUPLICATED ROWS IN A TABLE?