SQL - User Defined Functions

Refer the Source Video: SQL Server Programming Part 7 - User Defined Functions
(Sql Server 2012)

Sql Server has builtin Functions under Database node.

Programmability-Functions-System Functions.

SELECT FilmName,FilmReleaseDate,DATENAME(DW,FilmReleaseDate) + ' '+ DATENAME(D,FilmReleaseDate) + ' '+ DATENAME(M,FilmReleaseDate)+ ' '+ DATENAME(YY,FilmReleaseDate) FROM tblFilm

2.Instead of writing every time this , 

2 ways to create custom functions, but the template is not that friendly.

Programmability-Functions-Scalar-valued-Function

note:scalar:return one value(string,number or date)

******************

Simple Function:Defining function

******************

USE Movies

GO

CREATE FUNCTION fnLongDate( @FullDate AS DATETIME)

RETURNS VARCHAR(MAX)

AS

BEGIN

--RETURN DATENAME(DW,FilmReleaseDate) + ' '+ DATENAME(D,FilmReleaseDate) + ' '+ DATENAME(M,FilmReleaseDate)+ ' '+ DATENAME(YY,FilmReleaseDate) 

--find and replace 

RETURN DATENAME(DW, @FullDate) + ' '+ DATENAME(D, @FullDate) + ' '+ DATENAME(M, @FullDate)+ ' '+ DATENAME(YY, @FullDate) 

END

--

Execute so it creates.So fresh the windows to show.(Edit menu-InteliSense-Refresh Local Cache)

--

Test it.

SELECT FilmName,FilmReleaseDate,[dbo].[fnLongDate](FilmReleaseDate) from tblFilm

-----------

Modifying a Function:

-----------

adding a suffix:

USE Movies

GO

ALTER FUNCTION fnLongDate( @FullDate AS DATETIME)

RETURNS VARCHAR(MAX)

AS

BEGIN

--RETURN DATENAME(DW,FilmReleaseDate) + ' '+ DATENAME(D,FilmReleaseDate) + ' '+ DATENAME(M,FilmReleaseDate)+ ' '+ DATENAME(YY,FilmReleaseDate) 

--find and replace 

RETURN DATENAME(DW, @FullDate) + ' '+ DATENAME(D, @FullDate) + 

CASE

WHEN DAY(@FullDate IN(1,21) THEN 'st'

WHEN DAY(@FullDate IN(1,22) THEN 'nd'

WHEN DAY(@FullDate IN(3,23) THEN 'rd'

else 'th'

END+' '+ 

DATENAME(M, @FullDate)+ ' '+ DATENAME(YY, @FullDate) 

END

--

execute

--

Test it.

SELECT FilmName,FilmReleaseDate,[dbo].[fnLongDate](FilmReleaseDate) from tblFilm

--

The same function can be used in many places.

---

SELECT ActorName,ActorDOB,dbo.fnLongDate(ActorDOB) FROM tblActor

***************

Complex Expressions:

***************

--refer the video

SELECT DirectorName,LEFT(DirectorName,CHARINDEX(' ',DirectorName)-1) FROM tblDirector

--does not work if there only firstname no lastname

--if CHARINDEX returns 0

***************

Using Variables & IF Statements:

***************

USE Movies

GO

CREATE FUNCTION fnFirstName( @FullName AS VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

  --where the space

DECLARE @SpacePosition AS INT

DECLARE @Answer AS VARCHAR(MAX)

SET @SpacePosition = CHARINDEX(' ',@FullName)

IF @SpacePosition = 0

SET @Answer = @FullName

ELSE

SET @Answer = LEFT(@FullName,@SpacePosition - 1)

RETURN Answer

END

--

Execute and then test it.

--

--In function if more code it takes more time.

SELECT ActorName, dbo.fnFirstName(ActorName) FROM tblActor