HWA2_P1_3.sql

/* -------------------------------------------------------------------------
   Yu Cheng
   ICS 321 Assignment 2
   October 23, 2008

   PART 1.3
   Create a user defined scalar function for personnel names. This function
   will take the Person Identifier as a parameters and return a formatted
   string containing the last name, first name and initial in the following
   format: LastName, Firstname [initial]. Any reference to Personnel names
   in the following sections will use this function. Name this function
   PersonName.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- Create a user defined scalar function for personnel names. This function
-- will take the Person Identifier as a parameters and return a formatted
-- string containing the last name, first name and initial in the following
-- format: LastName, Firstname [initial].

CREATE FUNCTION PersonName(@Id INT) RETURNS NVARCHAR(50) AS
BEGIN
	DECLARE @FirstName  NVARCHAR(20)
	DECLARE @LastName   NVARCHAR(20)
	DECLARE @MiddleName NCHAR(1)
	DECLARE @Result     NVARCHAR(50)

	SELECT @LastName   = LastName   FROM PERSON WHERE Id = @Id
	SELECT @FirstName  = FirstName  FROM PERSON WHERE Id = @Id
	SELECT @MiddleName = MiddleName FROM PERSON WHERE Id = @Id

	SET @Result = @LastName + ', ' + @FirstName
	IF LEN(@MiddleName) > 0
		SET @Result = @Result + ' ' + @MiddleName

	RETURN @Result
END
GO

-- e.g. In:  PRINT dbo.PersonName(1)
--      Out: Bock, Douglass B
--
--      In:  PRINT dbo.PersonName(2)
--      Out: Summer, Elizabeth

-- NOTE: On more than one occasion, the homework shows "FirstName, LastName
-- MiddleName".  Furthermore, the PersonnelAssignments.xls stores
-- information this way in the Name column.  For this reason, I define
-- another function here that matches those forms.

CREATE FUNCTION PersonNameReversed(@Id int) RETURNS NVARCHAR(50) AS
BEGIN
	DECLARE @FirstName  NVARCHAR(20)
	DECLARE @LastName   NVARCHAR(20)
	DECLARE @MiddleName NCHAR(1)
	DECLARE @Result     NVARCHAR(50)

	SELECT @LastName   = LastName   FROM PERSON WHERE Id = @Id
	SELECT @FirstName  = FirstName  FROM PERSON WHERE Id = @Id
	SELECT @MiddleName = MiddleName FROM PERSON WHERE Id = @Id

	SET @Result = @FirstName + ', ' + @LastName
	IF LEN(@MiddleName) > 0
		SET @Result = @Result + ' ' + @MiddleName

	RETURN @Result
END
GO

-- e.g. In:  PRINT dbo.PersonNameReversed(1)
--      Out: Douglass, Brock B
--
--      In:  PRINT dbo.PersonNameReversed(2)
--      Out: Elizabeth, Summer