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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 13, 2014 at 1:26 pm

  #1679701

  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: 183706

  More actions

  January 13, 2014 at 1:32 pm

  #1679705

  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

  January 13, 2014 at 1:35 pm

  #1679706

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

• Lowell

  SSC Guru

  Points: 323522

  More actions

  January 13, 2014 at 2:11 pm

  #1679716

  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

  January 13, 2014 at 2:26 pm

  #1679720

  This seemed to work Lowell. Thanks a bunch.