SCALAR FUNCTION HOW TO HANDLE NULL VALUES

--ALL FUNCTION IN SQL REQUIRES PARENTHESES
--ACCEPTS 0 OR MORE ARGUMENTS(SOME OF WHICH MAY BE OPTIONAL)

GETDATE() --TAKES NO ARGUMENTS--PARENTHESES IS REQUIRED

SELECT * FROM dbo.distance

--NESTED FUNCTION
SELECT LOWER(LEFT(LOCATION,3)) AS NEWLOC FROM distance

--SCALAR FUNCTION CAN BE USED WHERE EVER AN EXPRESSION CAN BE USED
--WE KNOW IT RETURNS ONE VALUE
--YOU CAN NEST THEM AS WE SEEN ABOVE
--ITS NOT A NESTED RESULT SET BUT ONE VALUE PER ROW



SELECT 1 + NULL-----NULL

SELECT 2 + 8------10

SELECT NULL + NULL ------NULL

--SO
UNKNOWN + KNOWN= UNKNOWN
KNOWN +KNOWN= KNOWN
UNKNOWN + UNKNOWN = UNKNOWN

--TBL_NAME
FIRSTNAME  LASTNAME
ANURAG        NAYAK
ABHI               NULL
NULL              NULL

SELECT FIRSTNAME +'  ' + LASTNAME FROM TBL_NAME

--O/P
ANURAG NAYAK
NULL
NULL

--SO THIS IS WHERE I THE COALESCE  AND ISNULL COMES INTO ROLE WHICH I HAVE EXPLAINED IN MY EARLIER BLOG.