[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5424
  • Last Modified:

Calling a stored proc with GetDate() as a parameter

I have a stored procedure written that accepts 2 datetime fields as parameters.

It works fine if I use real dates. i.e.:
exec updateduration '4-1-2004', '4-1-2004'

I want to call it using getdate() but it won't work.. why not?
i.e. : exec updateduration getdate(), getdate()
Line 1: Incorrect syntax near ')'.

?!?!HELP!
0
gozoliet
Asked:
gozoliet
1 Solution
 
1markmcCommented:
Looking at Books On Line it appears that the Execute statement can pass parameters that are either a value or a variable but not an expression as is getdate().  

perhaps you could store the result of getdate() to a variable before passing?
0
 
jschillmCommented:
try this:

declare @vardate1 datetime
declare @vardate2 datetime

Select @vardate1 = getdate(), @vardate2 = getdate()

Exec updateduration @vardate1, @vardate2

keep in mind that the getdate() function includes the date and time
0
 
ChrisFretwellCommented:
You can also call the stored procedure without passing the date value and just use getdate() inside the sproc where you were putting the passed variable. There isnt really a reason to pass getdate.
0
 
gozolietAuthor Commented:
So the answer is you just can't do that.

The other answers are also correct.

What I did was partly what CHrisFretwell suggested.. I put in a default parameter of null for those parameters, and if it was null, then i used getdate() inside the procedure.

Thanks all.
0
 
mgexpert1Commented:
ChrisFretwell asks why anybody would want to pass getDate()... a good example is a generic stored procedure that works with any date, say "getSalesForDate". one could pass '2010-02-22' if the sales for 22-feb-2010 are desired, and for TODAY's sales, one would *want* to pass getDate(). sadly, one would be disappointed to find that getDate() doesn't work as a parameter.

suboptimal solutions (such as declaring a variable, then passing the value of the variable, or making NULL a magic parameter value to mean getDate(), or instead of passing the date in as a datetime, pass it in as a string, and use 'TODAY' as a magic string) all exist to work around this sql server limitation.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now