User Defined Functions in SQL

 CREATE FUNCTION ScalarValueFunction()
RETURNS VARCHAR(50)
AS
BEGIN
      DECLARE @date DATETIME
 
      --CREATE TABLE #DateTable([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --DECLARE @DateTable AS TABLE([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      SET @date = GETDATE()
 
      RETURN @date
END
 
SELECT dbo.ScalarValueFunction() AS CurrentDate
/*
      1. It returns only one parameter value.
      2. It supports complex logic & in the end, returns only one parameter value.
      3. Cannot access temporary tables from within a function.
      4. It starts and ends with BEGIN...END block.
      5. It is directly used in the SELECT statement.
*/
 
 
CREATE FUNCTION InlineTableValueFunction()
RETURNS TABLE
AS
RETURN
(
      --DECLARE @date DATETIME --Throw an error
 
      --CREATE TABLE #DateTable([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --DECLARE @DateTable AS TABLE([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --SET @date = GETDATE()
 
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
      UNION
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
)
 
SELECT * FROM dbo.InlineTableValueFunction()
/*
      1. It returns the table type parameter value.
      2. It does not allow DECLARE & CREATE keyword.
      3. Cannot access temporary tables from within a function.
      4. It starts with the RETURN block.
      5. It allows only one SELECT statement result.
      6. It is used in the FROM statement.
      7. It is also called the inline table-valued function.
*/
 
CREATE FUNCTION MultiStatementTableValueFunction()
RETURNS @DateTable TABLE(CurrentDate VARCHAR(50), TomorrowDate VARCHAR(50))
AS
BEGIN
      DECLARE @date DATETIME
 
      --CREATE TABLE #DateTable([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --DECLARE @DateTable1 AS TABLE([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable1 VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      SET @date = GETDATE()
 
      INSERT INTO @DateTable
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
 
      INSERT INTO @DateTable
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
 
      RETURN
END
 
SELECT * FROM dbo.MultiStatementTableValueFunction()
/*
      1. It returns the table structure.
      2. Cannot access temporary tables from within a function.
      3. It starts and ends with BEGIN...END block.
      4. It is used in the FROM statement.
      5. It must have a RETURN keyword.
      6. The function body can have one or more than one statement.
      7. It is also called the table-valued function.

*/