-- 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?