asked on

Default parameter in SQL function

I have been tasked to create a report that compares the sum of values from the previous year, the current month and ytd in three columns. In my first stab at it, I created a ytd function and a monthly function. I'd like to refine this by creating one function that uses an either default parameter that I either won't use or an optional parameter. Something like

CREATE FUNCTION dbo.TESTFUNCTION(@PERIOD INT, @YEAR INT, @MONTH INT) <<< I would like @MONTH to be optional. If @PERIOD = 1 then I'm going for a yearly total and I don't need the month.

chapmandew

CREATE FUNCTION dbo.TESTFUNCTION(@PERIOD INT, @YEAR INT, @MONTH INT = 0)

Aaron Greene

ASKER

This is what I have now, but I'm having trouble with the IF...THEN statement.

CREATE FUNCTION dbo.HOURS(@YEAR INT,@MONTH INT = 0)
RETURNS REAL
AS
BEGIN
DECLARE @RESULT REAL
IF @MONTH = 0 THEN
SELECT @RESULT = ISNULL(SUM(X.HOURS),0)
FROM
(SELECT DATEPART(Yy, dbo.tblWorkOrder.txtWORKSTARTDATE) AS YEAR, DATEPART(Mm, dbo.tblWorkOrder.txtWORKSTARTDATE) AS MONTH,
dbo.tblWorkOrder.cboBUNIT AS UNIT, dbo.tblWorkOrder_Labor.intLABOR_CODE AS PAYTYPE, dbo.tblWorkOrder_Labor.txtHOURS AS HOURS
FROM dbo.tblWorkOrder INNER JOIN
dbo.tblWorkOrder_Labor ON dbo.tblWorkOrder.txtWORKORDER = dbo.tblWorkOrder_Labor.txtWORKORDER INNER JOIN
dbo.tblData_Employee ON dbo.tblWorkOrder_Labor.txtEMPLOYEEID = dbo.tblData_Employee.txtEMPLOYEEID) X
WHERE
X.YEAR = @YEAR
ELSE
SELECT @RESULT = ISNULL(SUM(X.HOURS),0)
FROM
(SELECT DATEPART(Yy, dbo.tblWorkOrder.txtWORKSTARTDATE) AS YEAR, DATEPART(Mm, dbo.tblWorkOrder.txtWORKSTARTDATE) AS MONTH,
dbo.tblWorkOrder.cboBUNIT AS UNIT, dbo.tblWorkOrder_Labor.intLABOR_CODE AS PAYTYPE, dbo.tblWorkOrder_Labor.txtHOURS AS HOURS
FROM dbo.tblWorkOrder INNER JOIN
dbo.tblWorkOrder_Labor ON dbo.tblWorkOrder.txtWORKORDER = dbo.tblWorkOrder_Labor.txtWORKORDER INNER JOIN
dbo.tblData_Employee ON dbo.tblWorkOrder_Labor.txtEMPLOYEEID = dbo.tblData_Employee.txtEMPLOYEEID) X
WHERE X.YEAR = @YEAR
AND X.MONTH = @MONTH
RETURN @RESULT
END

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial