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.
*/
