I. OBJECTIVES At the end of this activity, the students will be able to: 1. Understand the use of FUNCTIONS 2. Create SCALAR valued user defined FUNCTION 3. Create Inline and Multi-Statement Table-Valued FUNCTION II. DISCUSSION In SQL Server, a function is a stored program that you can pass parameters into and return a value. Functions are nothing but a Database object that is being created for implementing or handling certain types of complex functionalities. In general, functions are sets of SQL statements that only accept input parameters, depending on which it simply takes some sort of inputs and displays a result accordingly. To create a scalar-valued user-defined function in SQL Server, consider the following syntax: CREATE FUNCTION fynstignName> ( ) RETURNSEynastion_Data_triee AS BEGIN -- Declare the return variable here DECLARE@ResultVar RataTxae> -- Add the T-SQL statements to compute the return value --here -- Return the result of the function RETURN@ResultVar END The following example creates a simple function called AddTwoNumbers that accepts two integer parameters and returns an integer result. CREATE FUNCTION AddTwidymbers @A int, @B int RETURNS int AS BEGIN DECLARE QResult as int SELECT @Result = QA RETURN @Result END PROCEDURES 1. Create a new database Activity 02 . 2. Execute the following code to create the Students table needed for this activity; 3. In creating FUNCTIONs, DML Insert, Update, and Delete are not allowed. The return value is also mandatory and not optional compared with Stored Procedures. To create a simple function the SELECTs and RETURN the Allowance of the selected Student. Execute the following SQL code: CREATE FUNCTION ydfGetAllawance ( @id char (4) ) RETURNS money AS BEGIN DECLARE @retValue money SET @retValue =0 SELECT QretValue = Allowance FROM Students WHERE StudentID = @id RETURN @retValue END 4. Verify if your newly created function exists. 5. Functions can be used in SELECT and WHERE/HAVING clause. To use this udfGetAllowance function, try these following codes. SELECT dRoudfGetAllowance ('1601') as Allowance Or SELECT * FROM Students 6. Functions can also return a Table. To return a result set of Students using a function, try this following code. 7. Verify that the newly created function exists. Programmability Stored Procedures Functions Table-valued Functions m dbo.udfGetStudents Scalar-valued Functions m dbo.udfGetAllowance Aggregate Functions System Functions 8. To use a function that RETURNS a table in a SELECT statement, try these following codes. SELECT*FROMdboudfGetstudents(OH) Or SELECT FirstName, Allowance FROM dbowdfGetstudents( ' OH ') WHERE Allowance > 100 IV. SUPPLEMENTARY ACTIVITY Execute the following code to create the required Products table for this activity. 1. Insert three (3) records similar to the sample table content shown below. [I] 2. Create a user-defined function that accepts two (2) parameters @category and @deliveredDate. Your user-defined function should generate (RETURNS) the next ProdCode in sequence when called. Consider the following illustration: Sample function call: SELECT dbounfGeneratePcodSode('electronics', '3/15/2021') Would produce a RETURN value of: EL210004 *** HINT: You need to use some of the built-in functions you learned in the previous course (ISDBASE) to accomplish this task. Example: You could use any built-in function that counts how many records are already in your table and increment it by one. You can also use string functions like substring to get only selected characters