SQL Server Performance Forum – Threads Archive
Passing the current date as a parameter
Hi I would like to create a stored procedure that accepts the current date as a parameter. something like: create procedure proc_name@rundate datetime = getdate
as
.
.
.
.
go and then run it with execute proc_name However, doing it this way I get ‘syntax error converting datetime from character string. Can anyone help / point me in the right direction please. PS
If I don’t specify a default, and then put the parameter at the end of the execute statement as below it works fine, but this kind of defeats the object of automating the procedure in the first place! create procedure proc_name
@rundate datetime
as
.
.
.
.
go execute proc_name ‘2005-12-20’
Unless your database is accessed from different time zones, I can’t see why you would need to feed the current date to SQL Server – it is aware of the current date, so no need to pass it as a parameter. Why not use create procedure proc_name
(@rundate datetime)
as SET @rundate = ISNULL(@rundate, GETDATE()) ….. GO
You can use CONVERT(VARCHAR(10), GETDATE(), 120) to drop the time part, if you’re only interested in the date.
Try this: create procedure proc_name
@rundate datetime = null
as if @rundate is null set @rundate = getdate() .
.
.
.
quote:Originally posted by MooreCock
Hi
I would like to create a stored procedure that accepts the current date as a parameter.
something like:
create procedure proc_name
@rundate datetime = getdate
as
.
.
.
.
go and then run it with execute proc_name However, doing it this way I get ‘syntax error converting datetime from character string. Can anyone help / point me in the right direction please. PS
If I don’t specify a default, and then put the parameter at the end of the execute statement as below it works fine, but this kind of defeats the object of automating the procedure in the first place! create procedure proc_name
@rundate datetime
as
.
.
.
.
go execute proc_name ‘2005-12-20’
@rundate datetime = getdate
as
.
.
.
.
go and then run it with execute proc_name However, doing it this way I get ‘syntax error converting datetime from character string. Can anyone help / point me in the right direction please. PS
If I don’t specify a default, and then put the parameter at the end of the execute statement as below it works fine, but this kind of defeats the object of automating the procedure in the first place! create procedure proc_name
@rundate datetime
as
.
.
.
.
go execute proc_name ‘2005-12-20’
Karl Grambow www.sqldbcontrol.com
You cannot use a function like GETDATE() as default for a parameter of a stored procedure.
Alternatively to Adriaan’s suggestion you can use
ALTER PROCEDURE proc_name
@rundate datetime = NULL
AS
IF @rundate IS NULL
SELECT GETDATE()
ELSE
SELECT @rundate
GO … —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
ALTER PROCEDURE proc_name @rundate datetime = NULL
AS
select ISNULL(@rundate,getdate())
Also Create procedure proc_name
@rundate datetime
as
select @rundate as Today
GO When executing the sp, you need to use DateTime variable to pass value to parameter Declare @date DateTime
set @date =getdate()
Exec proc_name @date Madhivanan Failing to plan is Planning to fail
Thanks for all the replies.<br /><br />What I omitted, that some of you picked up on, was that what I really wanted to do was pass a date to the procedure but use the current date as default if I didn’t put a date in. I’ll try your suggestions tomorrow.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
With approach mention there is a possibility to be affected by parameter sniffing. Better make sure date is always passed when stored procedure is executed. I think the sp caller can pass current date if it is needed.
]]>