Converting a stored procedure into user defined function

Question

Hi, i need help in converting a stored procedure to a function,

CREATE PROCEDURE [dbo].[FunGetEmpExp]  ( @fromdate DATETIME,@todate DATETIME)
AS
BEGIN 
    DECLARE @TotalYears VARCHAR(2) 
    DECLARE @TotalMonths VARCHAR(2) 
    DECLARE @TotalDays VARCHAR(2), @Duration varchar(10) 

    SET @TotalYears = null 
    SET @TotalMonths = NULL 
    SET @TotalDays = NULL 
    SET @todate = DATEADD(day,1,@todate)

    SELECT @TotalYears = CASE WHEN Month(@fromdate) <= Month(@Todate) AND (Day(@fromdate)<=Day(@Todate)) THEN DateDiff(YEAR,@fromdate,@Todate) 
    ELSE DateDiff(YEAR,@fromdate,@Todate) - 1 END 

    SELECT @TotalMonths = CASE WHEN (Day(@fromdate)<=Day(@Todate)) then DateDiff(MONTH,@fromdate,@Todate)- @TotalYears*12 ELSE DateDiff(MONTH,@fromdate,@Todate) - @TotalYears * 12 - 1 END 

    SELECT @TotalDays = DateDiff(DAY,DateAdd(MONTH, CASE WHEN (Day(@fromdate)<=Day(@Todate)) then DateDiff(MONTH,@fromdate,@Todate)-(DateDiff(YEAR,@fromdate,@Todate))*12 

    ELSE DateDiff(MONTH,@fromdate,@Todate)-(DateDiff(YEAR,@fromdate,@Todate))*12-1 end , DateAdd(YEAR,(DateDiff(YEAR,@fromdate,@Todate)),@fromdate)),@Todate) 

    IF (@TotalMonths >= 12) 
    BEGIN  
        SET @TotalYears = @TotalYears + 1 SET @TotalMonths = @TotalMonths - 12 
    END 

    SELECT @FromDate, @Todate, @TotalYears Yrs, @TotalMonths Mont, @TotalDays Days , @TotalYears + ' years,' + @TotalMonths + ' months,' + @TotalDays+ ' days' 
END 

the return value of the function should be [The Number Of Days, Months and Years Between Two Dates ]

eg:

exec FunGetEmpExp '2010-03-26','2011-01-31'
Return Value:0 years,10 months,6 days

Answer 1

You want to create a Table-Valued function

Answer 2

Fairly straightforward:

1. Change CREATE PROCEDURE to CREATE FUNCTION.
2. Add a returns datatype after the input parameters, so @todate DATETIME) becomes @todate DATETIME) RETURNS VARCHAR(255).
3. Change your final select to return the output value - so SELECT @FromDate, @Todate, @TotalYears Yrs, @TotalMonths Mont, @TotalDays Days , @TotalYears + ' years,' + @TotalMonths + ' months,' + @TotalDays+ ' days' becomes RETURN @TotalYears + ' years,' + @TotalMonths + ' months,' + @TotalDays+ ' days'.
4. Compile function.

You should now be able to use the function in a select statement - eg.

select [dbo].[FunGetEmpExp]('2010-03-26','2011-01-31')

should return '0 years,10 months,6 days'.