set default parameter for function parameter

Question

set default parameter for function parameter

ccmret

Right there with Babe

Points: 759
More actions
January 13, 2014 at 1:11 pm

#396102

Hello I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. Any ideas on how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

ccmret (1/13/2014)
Hello I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. Any ideas on how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO

What seems to be your problem? Are you getting an error message? If so, what is it?

Luis Cazares SSC Guru Points: 183686 More actions · Answer 2

I can't find the reference (so I might be wrong), but I'm sure you can't assign values from a function when declaring parameter defaults.

EDIT: Reference seems to be only for CREATE PROCEDURE where it states: "The default value must be a constant or it can be NULL". However, this remark is not made in CREATE FUNCTION.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ccmret Right there with Babe Points: 759 More actions · Answer 3

I get incorrect syntax near keyword CONVERT and Must declare the scalar variable @startdate

Lowell SSC Guru Points: 323482 More actions · Answer 4

Luis pointed it out i think; the assignment must be a static value or constant;

a convert cannot be used, even if it was a static date, nore getdate,

and when you call it with defaults, you'd need to use the default keyword, since the parameters are manditory for a function

something like this looks like it is along the lines of what you need:

Create Function HR.Equipment

(

@startdate Date =NULL,

@enddate Date =NULL

)

RETURNS TABLE AS RETURN

(

SELECT

EquipID,

EmpName,

IssueDate

FROM HR.Equipment

WHERE IssueDate <=ISNULL(@StartDate,DATEADD(DAY,-1,GETDATE())) and IssueDate >=ISNULL(@enddate,DATEADD(DAY,-90,GETDATE()))

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ccmret Right there with Babe Points: 759 More actions · Answer 5

ccmret

Right there with Babe

Points: 759

January 13, 2014 at 2:26 pm

#1679720

This seemed to work Lowell. Thanks a bunch.