How do I pass in today's date into a stored procedure?

Question

How do I pass in today's date into a stored procedure?

Rod at work

SSC-Dedicated

Points: 34008
More actions
November 21, 2008 at 4:05 pm

#196566

I'm trying a SQL Job step in which I want to call a stored procedure and pass in some parameters. One of them will be the current date and time, and so I thought I would pass in a getdate() function. So I was testing this in a SQL query in SSMS:
spMySP 2009, getdate()
but I got the following error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
So, how do I pass in today's date/time into a stored procedure in the SQL query? And ultimately into the same thing in my SQL job?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Rod at work (11/21/2008)
I'm trying a SQL Job step in which I want to call a stored procedure and pass in some parameters. One of them will be the current date and time, and so I thought I would pass in a getdate() function. So I was testing this in a SQL query in SSMS:
spMySP 2009, getdate()
but I got the following error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
So, how do I pass in today's date/time into a stored procedure in the SQL query? And ultimately into the same thing in my SQL job?

declare @TDate datetime;

set @TDate = getdate();

exec spMySP 2009, @TDate; -- Okay, I wasn't all there when I wrote this and forgot the exec

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Now there's a Hall of Fame answer! :D:P

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Jack Corbett (11/21/2008)
Now there's a Hall of Fame answer! :D:P

What?? I didn't do nothin'. -- (inside joke, anyone interested in personal happens, PM me I'll fill you in)

Garadin One Orange Chip Points: 29613 More actions · Answer 4

Also, keep in mind that you can always use GETDATE() within a stored procedure to grab the current Date/Time without having to pass it in.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 5

Garadin (11/22/2008)
Also, keep in mind that you can always use GETDATE() within a stored procedure to grab the current Date/Time without having to pass it in.

That is true, but I want the flexibility to be able to pass in any date, not just the current date/time, thus I want a parameter.

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 6

Lynn, this is a very simple follow-up question, but I've got to ask it. In the one other SQL Job I've written I've got something like:

spMySP 2009, 4

but you have

exec spMySP 2009, 4

Why include the exec?

Kindest Regards, Rod Connect with me on LinkedIn.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Rod at work (11/24/2008)
Lynn, this is a very simple follow-up question, but I've got to ask it. In the one other SQL Job I've written I've got something like:
spMySP 2009, 4
but you have
exec spMySP 2009, 4
Why include the exec?

Technically you can do it either way, but if you want to access the return value of the SP you need the Exec and using it is probably a goo d habit to develop.

Lynn may have an even better explanation.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Jack Corbett (11/24/2008)
Rod at work (11/24/2008)
Lynn, this is a very simple follow-up question, but I've got to ask it. In the one other SQL Job I've written I've got something like:
spMySP 2009, 4
but you have
exec spMySP 2009, 4
Why include the exec?
Technically you can do it either way, but if you want to access the return value of the SP you need the Exec and using it is probably a goo d habit to develop.
Lynn may have an even better explanation.

Actually, I was just going to refer Rod to Books Online and the EXECUTE statement (and you should still go there and read it).

spMySP 2009, 4 works fine if the stored procedure is the first line in a batch. If you, however, have a script like this:

declare @AVar int;

set @AVar = 4;

spMySP 2009, @AVar;

it will fail. To make the above code work, you need to do this:

declare @AVar int;

set @AVar = 4;

exec spMySP 2009, @AVar; -- or execute spMySP 2009, @AVar

Also, in your declaration of the stored procedure, you could do this:

create procedure dbo.spMySP (

@FirstParam int,

@DateParam datetime = null

)

as

begin

set @DateParam = coalesce(@DateParam, getdate())

...

end