How To Create User Defined Function In SQL Server

CREATE FUNCTION SumOfTwoNumber
( @a int, @b int )
RETURNS int
AS
BEGIN
RETURN (@a+@b)
END



==============
CREATE FUNCTION SumOfTwoNumber ( @a int, @b int )
RETURNS int
AS
BEGIN
WHILE @a <100 BEGIN SET @a =@a +1 END RETURN (@a+@b) END go SELECT dbo. SumOfTwoNumber(1,7) AS SumOfTwoValues SumOfTwoValues -------------- 107 =============== USE Northwind go CREATE FUNCTION fx_Customers_ByCity ( @City nvarchar(15) ) RETURNS table AS RETURN ( SELECT CompanyName FROM Customers WHERE City =@City ) go SELECT * FROM fx_Customers_ByCity('London') CompanyName ---------------------------------------- Around the Horn . . . Seven Seas Imports ======================= USE Northwind go CREATE FUNCTION fx_OrdersByDateRangeAndCount ( @OrderDateStart smalldatetime, @OrderDateEnd smalldatetime, @OrderCount smallint ) RETURNS @OrdersByDateRange TABLE ( CustomerID nchar(5), CompanyName nvarchar(40), OrderCount smallint, Ranking char(1) ) AS BEGIN --Statement 1 INSERT @OrdersByDateRange SELECT a.CustomerID, a.CompanyName, COUNT(a.CustomerID)AS OrderCount, 'B' FROM Customers a JOIN Orders b ON a.CustomerID =b.CustomerID WHERE OrderDate BETWEEN @OrderDateStart AND @OrderDateEnd GROUP BY a.CustomerID,a.CompanyName HAVING COUNT(a.CustomerID)>@OrderCount

--Statement 2
UPDATE @OrdersByDateRange
SET Ranking ='A'
WHERE CustomerID IN (SELECT TOP 5 WITH TIES CustomerID
FROM (SELECT a.CustomerID,
COUNT(a.CustomerID)AS OrderTotal
FROM Customers a
JOIN Orders b ON a.CustomerID =b.CustomerID
GROUP BY a.CustomerID) AS DerivedTable
ORDER BY OrderTotal DESC)
RETURN
END
==================
SELECT *
FROM fx_OrdersByDateRangeAndCount ('1/1/96','1/1/97',2)
ORDER By Ranking

CustomerID CompanyName OrderCount Ranking
---------- ------------------------------ ---------- -------
ERNSH Ernst Handel 6 A
FOLKO Folk och fä HB 3 A
HUNGO Hungry Owl All-Night Grocers 5 A
QUICK QUICK-Stop 6 A
SAVEA Save-a-lot Markets 3 A
SEVES Seven Seas Imports 3 B
SPLIR Split Rail Beer &Ale 5 B
...
=============================

CREATE FUNCTION fx_LDOM
( @Date varchar(20) )
RETURNS datetime
AS
BEGIN
--ensure valid date
IF ISDATE(@Date) = 1
BEGIN
--determine first day of month
SET @Date = DATEADD(day,-DAY(@Date)+1,@Date)
--determine last day of month
SET @Date = DATEADD(day,-1,DATEADD(month,1,@Date))
END
ELSE
SET @Date = '1/1/80'

RETURN @Date
END
=====================
CREATE TABLE fx_Testing (DateValue datetime)
go
INSERT fx_Testing VALUES ('1/1/01')
INSERT fx_Testing VALUES ('2/15/01')
INSERT fx_Testing VALUES ('2/15/02')
INSERT fx_Testing VALUES ('2/15/03')
INSERT fx_Testing VALUES ('2/15/04')

SELECT DateValue,
dbo.fx_LDOM(DateValue) AS LDOM,
DATEDIFF(day,DateValue,dbo.fx_LDOM(DateValue)) AS DaysLeftInMonth
FROM fx_Testing

DateValue LDOM DaysLeftInMonth
------------------------ ----------------------- ---------------
2001-01-01 00:00:00.000 2001-01-31 00:00:00.000 30
2001-02-15 00:00:00.000 2001-02-28 00:00:00.000 13
2002-02-15 00:00:00.000 2002-02-28 00:00:00.000 13
2003-02-15 00:00:00.000 2003-02-28 00:00:00.000 13
2004-02-15 00:00:00.000 2004-02-29 00:00:00.000 14