DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create a counting function?

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
 

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE