stored proc default input param error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored proc default input param error

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-04-03 : 12:02:40

Hi,

I got this syntax error on the input line I have comment out

CREATE PROCEDURE dbo.Sel_BCH_By_DAT
--@batchDate date = convert(date, getdate())
@batchDate date = null
AS
select xx from tblA where dateA<= isnull(@batchdate, getdate())
--select xx from tblA where dateA<= @batchDate

I want to set a default input param with date datatype and current date as its value.

The error is
Incorrect syntax near the keyword 'convert'.

and even I removed the convert as
@batchDate date = getdate()

It got error out at (

what am I missing here?

Thank!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-03 : 12:56:33

You need a comma after the getdate().

Just do the convert in the body of the stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-03 : 14:01:57

Quoting from MSDN: "The default value must be a constant or it can be NULL. The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure." So do what you did, or if @batchdate is used in multiple places in the stored procedure, at the beginning of the procedure add a statement like:

IF (@batchDate IS NULL) SET @batchDate = CONVERT(DATE,GETDATE());

http://technet.microsoft.com/en-us/library/ms187926.aspx

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-04-03 : 14:49:28

I see. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources