BIDATA 201A: TSQL: FUNCTIONS

--****************** SQL Programming *********************--

-- This file explains how to create and use Functions

--**********************************************************--

-- SQL Server 2000 and 2005 supports three types of user-defined functions:

      --Scalar functions

      --Inline table-valued functions

      --Multistatement table-valued functions

-- Create the simple function

CREATE FUNCTION fn_NewRegion

(@myinput nvarchar(30))

RETURNS nvarchar(30)

BEGIN

  IF @myinput IS NULL

  SET @myinput = 'Not Applicable'

  RETURN @myinput

END  

USE [AW_Project01_OLTP_DB]

GO

-- Using just a Select statement (note the Nulls on the Region column)

SELECT LastName, City, Region, Country

FROM Employees

-- Using the function to fill in the Null values

SELECT LastName, City, dbo.fn_NewRegion(Region) AS Region,

   Country

FROM Employees

-- Altering a function

ALTER FUNCTION fn_NewRegion

(@myinput nvarchar(30))

RETURNS nvarchar(30)

BEGIN

  IF @myinput IS NULL

  SET @myinput = 'International'

  RETURN @myinput

END

-- Using the function to fill in the Null values

SELECT LastName, City, dbo.fn_NewRegion(Region) AS Region,

   Country

FROM Employees

-- Dropping a function

DROP FUNCTION dbo.fn_NewRegion