Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the creation and test of a user-defined function.

-- T-SQL integer scalar-valued user-defined function
-- MSSQL inline function - count aggregate function
USE AdventureWorks;
GO
 
CREATE FUNCTION fnPurchaseOrdersByEmployee
               (@EmpID INT)
RETURNS INT
AS
  BEGIN
    RETURN
      (SELECT   COUNT(* ) AS 'PurchaseOrdersByEmployee'
       FROM     Purchasing.PurchaseOrderHeader
       WHERE    EmployeeID = @EmpID
       GROUP BY EmployeeID)
  END
GO
 
-- Test UDF
SELECT   Staff = LastName+', '+FirstName,
         [PO-s] = dbo.fnPurchaseOrdersByEmployee(EmployeeID)
FROM     HumanResources.Employee e
  INNER JOIN Person.Contact c
    ON e.ContactID = c.ContactID
WHERE dbo.fnPurchaseOrdersByEmployee(EmployeeID)  is not null
ORDER BY EmployeeID
GO
/* Results
 
 
Staff                   PO-s
Sandberg, Mikael        361
Rao, Arvind             164
Meisner, Linda          400
Ogisu, Fukiko           362
Hee, Gordon             360
Pellow, Frank           361
Kurjan, Eric            360
Hagens, Erin            361
Miller, Ben             360
Hill, Annette           362
Hillmann, Reinout       401
Word, Sheela            160
*/
 
-- Cleanup
DROP FUNCTION fnPurchaseOrdersByEmployee
GO
 

------------