default parameter in a function

Question

Post reply

default parameter in a function

Paul McCurdy

Ten Centuries

Points: 1377
More actions
December 11, 2010 at 3:25 pm

#229572

I want to create a function that returns all values from a table as at a specific date. ANd if the user doesn't pass a date, it will default to today. If I try this:
CREATE FUNCTION myTbl
(@theDate smalldatetime = CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS SMALLDATETIME) )
RETURNS TABLE
AS
RETURN
(
SELECT scd.SalesCd
FROM dbo.dimSalesCodes scd
WHERE scd.endDate > @TheDate AND scd.startDate <= @TheDate
it returns error on '(' and says "A RETURN statement with a return value cannot be used in this context."
Is there a way to do this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Specifying an expression rather than a value for the parameter default. Try this instead:

ALTER FUNCTION myTbl

(@theDate smalldatetime = NULL)

RETURNS TABLE

AS

RETURN

(

SELECT scd.SalesCd

FROM dbo.dimSalesCodes scd

WHERE scd.endDate > ISNULL(@TheDate, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))

AND scd.startDate <= ISNULL(@TheDate, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))

)

GO

SELECT * FROM myTbl('20101101') -- 1

SELECT * FROM myTbl(NULL) -- 2

If you don't like the default date expression appearing twice in the filter, then you can use APPLY:

ALTER FUNCTION myTbl

(@theDate smalldatetime = NULL)

RETURNS TABLE

AS

RETURN

(

SELECT scd.SalesCd

FROM dbo.dimSalesCodes scd

CROSS APPLY(SELECT Testdate = ISNULL(@TheDate, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)))iTVF

WHERE scd.endDate > iTVF.Testdate

AND scd.startDate <= iTVF.Testdate

)

GO

SELECT * FROM myTbl('20101101') -- 1

SELECT * FROM myTbl(NULL) -- 2

A little sample data for testing (you are likely to get answers far more quickly if you include this in your posts):

CREATE TABLE dimSalesCodes (SalesCd INT, startDate DATE, endDate DATE)

INSERT INTO dimSalesCodes (SalesCd, startDate, endDate)

SELECT 1, '20101101', '20101130' UNION ALL

SELECT 2, '20101201', '20101231' UNION ALL

SELECT 3, '20110101', '20110131'

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 2

Thanks Chris!

And you're right, I'll include samples in future 😉

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 3

Is there any way of setting it up so that user can leave off parameter entirely.

e.g. If I try SELECT * FROM myTbl it errors with 'Parameters were not supplied'

Using SELECT * FROM myTbl(null) or SELECT * FROM myTbl('2010-12-9') works OK