SP default values question - SQL Server Forums

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

SP default values question

Author

Topic

lynda
Starting Member

21 Posts

Posted - 2009-06-09 : 12:28:05

I need to set default values for 2 parms in my SP. So I do this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE FirstDataReport
-- Add the parameters for the stored procedure here
@month int = DATEPART(month,GETDATE()),
@year int = DATEPART(year,GETDATE())
AS
BEGIN

When I do this, I get:

Msg 102, Level 15, State 1, Procedure FirstDataReport, Line 3
Incorrect syntax near '('.

On the @month declaration. Can anyone shed any light here?

Thanks!

Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 12:33:43

you cant give expressions for default value of sp. you can do it like this however


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE FirstDataReport 
-- Add the parameters for the stored procedure here
@month int = NULL, 
@year int = NULL
AS
BEGIN
SELECT @month=COALESCE(@month,DATEPART(month,GETDATE())),
@year=COALESCE(@year,DATEPART(year,GETDATE()))

lynda
Starting Member

21 Posts

Posted - 2009-06-09 : 12:51:15

Excellent. Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 13:23:15

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources