/* Function calculates internal marks for a student based on his attendance. Showcase: 1. Function with a parameter and returning INT value. 2. IF...ELSE construct. 3. RETURN clause */ CREATE FUNCTION INTERNAL_MARKS_CALCULATOR (@ROLL INT) RETURNS INT AS BEGIN DECLARE @VAR FLOAT DECLARE @RET INT SET @VAR = (SELECT CONVERT(FLOAT,NUMBER_OF_DAYS_PRESENT)/CONVERT(FLOAT,TOTAL_DAYS) FROM STUDENT_ATTENDANCE_VIEW WHERE ROLL_NO=@ROLL) IF(@VAR > 0.7) SET @RET = 20 ELSE SET @RET = 10 RETURN @RET END GO /* Function calculates date of payment of FEES based on Date of Joining. Showcase: 1. Function returning DATE data type. */ CREATE FUNCTION FEES_PAYMENT_DATE (@ROLL INT) RETURNS DATETIME AS BEGIN DECLARE @VAR DATETIME SET @VAR = (SELECT DATEADD(DAY,10,DOJ) FROM STUDENT WHERE ROLL_NO=@ROLL) RETURN @VAR END GO /* Procedure to compute total marks of a student. Showcase: 1. Procedure with multiple parameters 2. DECLARE statements 3. Function call in a SET statement */ CREATE PROCEDURE COMPUTE_TOTAL_MARKS @ROLL INT, @MARKS INT AS DECLARE @VAR INT DECLARE @TOTAL INT SET @VAR = DBO.INTERNAL_MARKS_CALCULATOR(@ROLL) SET @TOTAL = @VAR+@MARKS PRINT 'TOTAL MARKS ' +CONVERT(VARCHAR,@TOTAL) GO /* Assigning a class for a student who is enrolling afresh. Showcase: 1. DECLARE statements (data types DATETIME and VARCHAR) 2. DATEDIFF User-defined function 3. GETDATE() User-defined function 4. SIMPLE CASE function 5. PRINT statement 6. INSERT with VALUES statement. 7. SELECT statement with TOP clause */ CREATE PROCEDURE ASSIGN_CLASS_FOR_STUDENT @ROLL INT AS DECLARE @DOB DATETIME DECLARE @AGE INT DECLARE @CLASS VARCHAR(10) DECLARE @CID INT DECLARE @SEC VARCHAR(10) SET @DOB=(SELECT DOB FROM STUDENT WHERE ROLL_NO=@ROLL) SET @AGE = DATEDIFF(YY,@DOB,GETDATE()) SET @CLASS=CASE @AGE WHEN 3 THEN 'PRE-KG' WHEN 4 THEN 'UKG' WHEN 5 THEN 'LKG' WHEN 6 THEN 'CLASS-1' WHEN 7 THEN 'CLASS-2' WHEN 8 THEN 'CLASS-3' WHEN 9 THEN 'CLASS-4' WHEN 10 THEN 'CLASS-5' WHEN 11 THEN 'CLASS-6' WHEN 12 THEN 'CLASS-7' WHEN 13 THEN 'CLASS-8' WHEN 14 THEN 'CLASS-9' WHEN 15 THEN 'CLASS-10' WHEN 16 THEN 'CLASS-11' ELSE 'CLASS-12'END PRINT 'CLASS ASSIGNED FOR '+CONVERT(VARCHAR,@ROLL) +' IS '+@CLASS SET @CID = (SELECT CLASS_ID FROM CLASS WHERE CLASS_NAME=@CLASS) SET @SEC = (SELECT TOP 1 SECTION_NAME FROM CLASS_SECTION WHERE CLASS_ID=@CID) INSERT INTO STUDENT_CLASS VALUES(@ROLL,@CID,@SEC) GO /* Add a student to the database Showcase: 1. DEFAULT values for parameters in procedure 2. @@IDENTITY global varaible 3. Calling another PROCEDURE with argument from within procedure 4. Function call in SET clause */ CREATE PROCEDURE ADD_STUDENT @FNAME VARCHAR(100), @LNAME VARCHAR(100), @ADDR VARCHAR(200), @PH INT, @SEX VARCHAR(10) = 'MALE', @DOB DATETIME, @BLOOD VARCHAR(10) AS DECLARE @IDENT INT DECLARE @FEES_DATE DATETIME INSERT INTO STUDENT VALUES (@FNAME, @LNAME, @ADDR,@PH,@SEX,@DOB,GETDATE(),@BLOOD) SET @IDENT = @@IDENTITY EXEC ASSIGN_CLASS_FOR_STUDENT @IDENT SET @FEES_DATE = DBO.FEES_PAYMENT_DATE(@IDENT) PRINT 'FEES SHOULD BE PAID ON OR BEFORE '+CONVERT(VARCHAR,@FEES_DATE) GO /* Add an examination schedule to database Showcase: 1. RAISERROR statement */ CREATE PROCEDURE ADD_EXAM_SCHEDULE @CLASS INT, @NAME VARCHAR(20), @E_DATE DATETIME AS IF @CLASS < 0 RAISERROR ('CLASS ID CANNOT BE LESS THAN 0.', 16, 1) INSERT INTO EXAM_SCHEDULE VALUES(@CLASS, @NAME,@E_DATE) GO /* Prepares grade for students Showcase: 1. Temporary tables within procedure 2. DECLARE CURSOR statements 3. OPEN statement 4. FETCH from CURSOR statement 5. WHILE statement 6. @@FETCH_STATUS usage 7. BREAK statement 8. CLOSE and DEALLOCATE cursor statements 9. CONVERT in-built function usage 10. SEARCHED CASE function */ CREATE PROCEDURE ANNUAL_GRADE AS DECLARE @ROLL INT DECLARE @MARKS INT DECLARE @TOTAL INT DECLARE @VAR FLOAT DECLARE @GRAD CHAR(10) DECLARE MARKS_CURSOR CURSOR FOR SELECT ROLL_NO, MARKS_SCORED,TOTAL_MARKS FROM STUDENT_MARKS_IN_EXAMS CREATE TABLE #TEMP_MARKS (ROLL_NO INT,MARKS INT, TOTAL INT, GRADE CHAR(10)) OPEN MARKS_CURSOR FETCH NEXT FROM MARKS_CURSOR INTO @ROLL,@MARKS,@TOTAL WHILE @@FETCH_STATUS = 0 BEGIN SELECT @VAR = CONVERT(FLOAT,@MARKS)/CONVERT(FLOAT,@TOTAL) IF(@VAR > 1) BREAK SET @GRAD=CASE WHEN @VAR > 0.9 THEN 'A' WHEN @VAR > 0.8 THEN 'B' WHEN @VAR > 0.7 THEN 'C' WHEN @VAR > 0.6 THEN 'D' WHEN @VAR > 0.5 THEN 'E' ELSE 'F' END INSERT INTO #TEMP_MARKS VALUES (@ROLL,@MARKS,@TOTAL, @GRAD) FETCH NEXT FROM MARKS_CURSOR INTO @ROLL,@MARKS,@TOTAL END CLOSE MARKS_CURSOR DEALLOCATE MARKS_CURSOR SELECT * FROM #TEMP_MARKS GO /* Showcase: 1. RETURN statement in procedure 2. GOTO statement 3. LABEL defintion */ CREATE PROCEDURE SET_ATTENDANCE @ROLL INT, @ATTENDED INT AS DECLARE @VAR INT IF @ATTENDED = 1 BEGIN INSERT INTO STUDENT_ATTENDANCE VALUES(@ROLL,GETDATE(),@ATTENDED) RETURN END ELSE GOTO ABSENT ABSENT: SET @VAR = (SELECT COUNT(*) FROM STUDENT_ATTENDANCE WHERE ROLL_NO=@ROLL AND ATTENDED=0) SET @VAR = @VAR +1 PRINT 'STUDENT WITH ROLL NUMBER : '+CONVERT(VARCHAR,@ROLL)+' HAS BEEN ABSENT FOR '+CONVERT(VARCHAR,@VAR)+ ' DAYS TILL DATE' INSERT INTO STUDENT_ATTENDANCE VALUES(@ROLL,GETDATE(),@ATTENDED) GO /* Showcase: 1. Update statement with MONEY data type. */ CREATE PROCEDURE INCREASE_FEES @CLASS INT, @INC MONEY AS UPDATE FEES SET FEES=FEES+@INC FROM FEES WHERE CLASS_ID=@CLASS GO /* Showcase: 1. DELETE statement 2. IF EXISTS statement 3. @@ROWCOUNT usage 4. @@ERROR usage */ CREATE PROCEDURE REMOVE_STUDENT @ROLL INT AS DECLARE @ROW INT IF EXISTS (SELECT * FROM STUDENT WHERE ROLL_NO=@ROLL) BEGIN DELETE FROM STUDENT WHERE ROLL_NO=@ROLL SET @ROW = @@ROWCOUNT PRINT 'NUMBER OF ROWS DELETED : '+CONVERT(VARCHAR,@ROW) END ELSE BEGIN SET @ROW=@@ERROR PRINT 'STUDENT WITH THE SPECIFIED ROLL NUMBER NOT FOUND'+CONVERT(VARCHAR,@ROW)+'.' END GO /* Showcase: 1. MAX in-built function 2. MIN in-built function 1. AVG in-built function */ CREATE PROCEDURE ANALYZE_MARKS @SUBJECT VARCHAR(50), @EXAM INT AS DECLARE @MAXI INT DECLARE @MINI INT DECLARE @AVG FLOAT SET @MAXI= (SELECT MAX(MARKS_OBTAINED) FROM STUDENT_MARKS WHERE EXAM_ID=@EXAM AND SUBJECT_NAME=@SUBJECT) SET @MINI= (SELECT MIN(MARKS_OBTAINED) FROM STUDENT_MARKS WHERE EXAM_ID=@EXAM AND SUBJECT_NAME=@SUBJECT) SET @AVG = (SELECT AVG(MARKS_OBTAINED) FROM STUDENT_MARKS WHERE EXAM_ID=@EXAM AND SUBJECT_NAME=@SUBJECT) PRINT 'MAXIMUM SCORE IN '+@SUBJECT +' IS '+CONVERT(VARCHAR,@MAXI) PRINT 'MINIMUM SCORE IN '+@SUBJECT +' IS '+CONVERT(VARCHAR,@MINI) PRINT 'AVERAGE SCORE IN '+@SUBJECT +' IS '+CONVERT(VARCHAR,@AVG) GO /* Showcase: 1. LEFT in-built function 2. RIGHT in-built function 3. LOWER in-built function 4. UPPER in-built function 5. SUBSTRING in-built function 6. LEN in-built function */ CREATE PROCEDURE FORMAT_NAME @ROLL INT AS DECLARE @RESULT VARCHAR(100) DECLARE @VAR INT SET @RESULT = (SELECT LEFT(FIRSTNAME,3) FROM STUDENT WHERE ROLL_NO=@ROLL) PRINT 'RESULT OF LEFT FUNCTION : '+@RESULT SET @RESULT = (SELECT RIGHT(FIRSTNAME,3) FROM STUDENT WHERE ROLL_NO=@ROLL) PRINT 'RESULT OF RIGHT FUNCTION : '+@RESULT SET @RESULT = (SELECT LOWER(FIRSTNAME) FROM STUDENT WHERE ROLL_NO=@ROLL) PRINT 'RESULT OF LOWER FUNCTION : '+@RESULT SET @RESULT = (SELECT UPPER(FIRSTNAME) FROM STUDENT WHERE ROLL_NO=@ROLL) PRINT 'RESULT OF UPPER FUNCTION : '+@RESULT SET @RESULT = (SELECT SUBSTRING(FIRSTNAME,1,2) FROM STUDENT WHERE ROLL_NO=@ROLL) PRINT 'RESULT OF SUBSTRING FUNCTION : '+@RESULT SET @VAR = (SELECT LEN(FIRSTNAME) FROM STUDENT WHERE ROLL_NO=@ROLL) PRINT 'RESULT OF LEN FUNCTION : '+CONVERT(VARCHAR,@VAR) GO /* Showcase: 1. CAST in-built function 2. ROUND in-built function 3. BITWISE AND operator 4. BITWISE OR operator 5. BITWISE XOR operator 6. Function returning multiple result set */ CREATE PROCEDURE FUNCTION_TEST AS DECLARE @VAR INT SET @VAR= (SELECT CAST(ROUND(MARKS_OBTAINED/TOTAL_MARKS,0) AS INT) FROM STUDENT_MARKS WHERE ROLL_NO=1) PRINT 'VALUE OF VAR WITH CAST AND ROUND WITH 0 AS ARGUMENT : '+CONVERT(VARCHAR,@VAR) SET @VAR= (SELECT CAST(ROUND(MARKS_OBTAINED/TOTAL_MARKS,1) AS INT) FROM STUDENT_MARKS WHERE ROLL_NO=1) PRINT 'VALUE OF VAR WITH CAST AND ROUND WITH 1 AS ARGUMENT : '+CONVERT(VARCHAR,@VAR) SELECT 2&3 SELECT 2|3 SELECT 2^3 GO