Sql-server – Microsoft SQL Server creating an Inline function

functionssql servert-sql

I am completely new to SQL where I have to write a user-defined function (UDF) that calculates a student's GPA for a given time frame. The Inputs are StudentId int, ClassStartDateStart datetime, and ClassStartDateEnd datetime. Where the output should be the student's GPA for all classes that were taken between ClassStartDateStart and ClassStartDateEnd. Also, supply the script to call this new function, passing it parameter values of your choice. I tried creating a code but don't know where to start. Here is what I have so far:

USE [Master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION GPAofStudents 
  (StudentID int, 
   ClassStartDateStart datetime, 
   ClassStartDateEnd datetime)
  RETURNS decimal(3,2) 
  AS
BEGIN 
DECLARE AvgGPA decimal(5,2); 
SET AvgGPA = (SELECT AVG(Class_GPA)
FROM Students_Classes 
  WHERE Student_ID = StudentID 
    AND Start_Date > ClassStartDateStart 
    AND Start_Date <= ClassStartDateEnd 
    AND Class_GPA > 0; 
    RETURN AvgGPA; 
END;

My table for the students is this:

CREATE TABLE [dbo].[Students_Classes](
    [Student_Class_ID] [int] IDENTITY(1,1) NOT NULL,
    [Student_ID] [int] NOT NULL,
    [Class_ID] [int] NOT NULL,
    [Start_Date] [date] NOT NULL,
    [Assignment1] [int] NULL,
    [Assignment2] [int] NULL,
    [Assignment3] [int] NULL,
    [Assignment4] [int] NULL,
    [Class_GPA] [int] NULL,
     CONSTRAINT [Student_Class_ID] PRIMARY KEY CLUSTERED 
(
USE [A_University_Database]
INSERT INTO Students_Classes
([Student_ID],[Class_ID],[Start_Date],[Assignment1],[Assignment2],[Assignment3],
 [Assignment4],[Class_GPA])
    VALUES
('1', '5', '2010-05-30', '86', '92', '69', '99', NULL),
('1', '6', '2010-05-30', '86', '92', '69', '99', NULL),
('2', '2', '2010-05-30', '99', '85', '91', '79', NULL),
('2', '7', '2010-05-30', '99', '85', '91', '79', NULL),
('3', '3', '2010-10-01', '67', '91', '71', '100', NULL),
('3', '4', '2010-10-01', '67', '91', '71', '100', NULL),
('4', '5', '2009-02-21', '56', '93', '72', '86', NULL),
('4', '6', '2009-02-21', '56', '93', '72', '86', NULL)

What I have to do is create an aggregate inline function that displays the students class GPA. Do I need to create a stored procedure first that way the inputs are recognized?

Best Answer

Do I need to create a stored procedure first that way the inputs are recognized?

You are missing @ symbols at the beginning of each parameter.

Find out more about writing functions here

Once you know the basics this article by Jeremiah Peschka explains the benefits of inline functions and much more. Basically if your table valued function is not inline then it will execute once for every row returned by the calling query. This can be very inefficient. An inline table valued function can be referred to as a parameterised view.

Your function is a scalar function which will always execute once for each row but it could be converted to an inline table valued function with the code below.

CREATE FUNCTION dbo.GPAofStudents 
  (@StudentID int, 
   @ClassStartDateStart date, 
   @ClassStartDateEnd date)

RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
    SELECT  @StudentID AS Student_ID,
            AVG(Class_GPA) AS GPA
    FROM    dbo.Students_Classes 
    WHERE   Student_ID = @StudentID 
            AND Start_Date > @ClassStartDateStart 
            AND Start_Date <= @ClassStartDateEnd 
            AND Class_GPA > 0;

Using the WITH SCHEMABINDING option will stop anyone from editing the schema used by your function and breaking it.

Related Question