Working with UDFs in SQL JOINs:

CREATE
TABLE TableOne
(

Col1 INT,
Col2
CHAR(5)

)

GO

INSERT TableOne VALUES (1, 'One')
INSERT TableOne VALUES (2, 'Two')
INSERT TableOne VALUES (3, 'Three')

GO
SELECT

* FROM TableOne

CREATE
FUNCTION dbo.udfTableTwo (@Id INT)

RETURNS
@tbl TABLE (TID INT, TChar CHAR(1))

AS
 
BEGIN
 
DECLARE @test INT

SET @test = @Id

WHILE @test >= 0

BEGIN

INSERT @tbl VALUES (@Id, CHAR(65+@test))

set @test = @test - 1

END
RETURN

END

 SELECT * FROM dbo.udfTableTwo(2)

SELECT a.*, b.* FROM TableOne a join dbo.udfTableTwo(2) b

ON a.col1=b.TID

DROP TABLE TableOne

GO

DROP FUNCTION dbo.udfTableTwo

--- Example for Sample Functions

GO

CREATE
FUNCTION [dbo].[ufn_GetDaysInMonth](

@CurrentDate DATETIME )

RETURNS INT

AS

BEGIN

DECLARE @ReturnDays INT

SET @ReturnDays =

CASE

WHEN MONTH(@CurrentDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN MONTH(@CurrentDate) IN (4, 6, 9, 11) THEN 30

ELSE

CASE WHEN (YEAR(@CurrentDate) % 4 = 0 AND YEAR(@CurrentDate) % 100 != 0) OR (YEAR(@CurrentDate) % 400 = 0) THEN 29

ELSE 28

END

END

RETURN @ReturnDays

END

 -- ExecutingSELECT

dbo.ufn_GetDaysInMonth('02/10/2012') No_Of_Days_In_Month

GO