Please Refer the Below Link which explains Scalar and Table value function.
https://msdn.microsoft.com/en-us/library/ms191320.aspx
Also Refer below sample SQLFunction For your understanding here we used Northwind Database. You can download it From below link.
Download and install Northwind Database .
We can use function as Scalar or Table Function.
As per your question you asked how to use Table function for select Single value or how to get single value also how we can use common functionality like we as for Join
We can use Table Function As result set And make the output result to use as per over requirement.
I have created two functions GetOrderDetails and GetCustomerDetails. Please check the below Sample Exaple for your reference.
-- SELECT * FROM GetOrderDetails (NULL)
-- SELECT * FROM GetOrderDetails (10248)
CREATE FUNCTION GetOrderDetails
(
@OrderID VARCHAR(15) = NULL
)
RETURNS @GetOrderDetails TABLE
( [OrderID] [int] NOT NULL
,SupplierID INT NOT NULL
,SupplierCompanyName VARCHAR(40) NOT NULL
,SupplierContactName VARCHAR(30) NOT NULL
,CategoryID INT NOT NULL
,CategoryName VARCHAR(15) NOT NULL
,ProductID INT NOT NULL
,[ProductName] [nvarchar] (40) NOT NULL
,[UnitPrice] [money] NOT NULL
,[Quantity] [smallint] NOT NULL
,[Discount] [real] NOT NULL)
AS
BEGIN
INSERT INTO @GetOrderDetails
SELECT OD.[OrderID]
,P.SupplierID
,S.CompanyName
,S.ContactName
,P.CategoryID
,C.CategoryName
,OD.ProductID
,P.[ProductName]
,OD.[UnitPrice]
,OD.[Quantity]
,OD.[Discount]
FROM [Order Details] AS OD
INNER JOIN Products P ON P.ProductID = OD.ProductID
INNER JOIN Suppliers S ON S.SupplierID = P.SupplierID
INNER JOIN Categories C ON C.CategoryID = P.CategoryID
WHERE ( OD.[OrderID] = @OrderID OR @OrderID IS NULL)
RETURN
END
GO
------------------------------------------------------------------------------
--SELECT * FROM GetCustomerDetails(NULL)
-- SELECT * FROM GetCustomerDetails(NULL)
ALTER FUNCTION GetCustomerDetails
(
@CustomerID VARCHAR(15) = NULL
)
RETURNS @Customers TABLE
( [CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL
,OrderId INT NULL
)
AS
BEGIN
INSERT INTO @Customers
SELECT Customers.CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,ORDERS.OrderId
FROM Customers LEFT OUTER JOIN ORDERS ON Customers.CustomerID = ORDERS.CustomerID
WHERE (Customers.CustomerID = @CustomerID OR @CustomerID IS NULL)
RETURN
END
GO
1. If we need a result set that we will use for join or UNION to another result set, table or view.
3. If we want to embed the returned value in a SELECT statement, you will need a function.
You cant do that in an SP. This is also true in most part of the SELECT statement like WHERE, HAVING, GROUP BY, etc.
/* Here we selecting Records from function By matching it in where clause */
SELECT * FROM GetCustomerDetails(NULL)
WHERE City = 'Mannheim '
/* We can use TABLE FUCTION With Table AND We can match the Table Fuction return value in Where clause To get result */
SELECT O.OrderID
,SupplierCompanyName
,SupplierContactName
,CategoryName
,ProductName
FROM ORDERS O
INNER JOIN GetOrderDetails (NULL) AS GOD
ON O.ORDERID = GOD.OrderId
WHERE GOD.ProductId IN (1,2,5)
GROUP BY O.OrderID,SupplierCompanyName,SupplierContactName,CategoryName,ProductName,SupplierID
HAVING SupplierID IN (1,2,3,4,5,6,7,8,9,10,11,1,2,13,14,15)
SELECT ORDERID
,CustomerID
,CustomerName
FROM ( SELECT ORDERID,CustomerID,(SELECT ContactName From Customers WHERE CustomerId = ORDERS.CustomerId ) CustomerName FROM ORDERS
WHERE ShipCountry = 'Germany'
UNION ALL
SELECT ORDERID,CustomerID,ContactName FROM GetCustomerDetails(NULL)
WHERE ORDERID IS NULL
) AS TBL
WHERE CustomerName LIKE 'Mari%'
