Blinding - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Blinding

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-12-23 : 09:35:17

I'm sure I'm missing something blindingly obvious but have looked all over with no success. I have a sproc which takes two parameters. I wanted them to default to the current date (i.e. getdate()) but that didn't work and BOL suggested that the defaults have to be constants.

I now have:-

CREATE PROCEDURE sproc_RMSReferrals
@StartMonth datetime,
@EndMonth datetime
AS
...

when I try to run

exec sproc_RMSReferrals getdate(), getdate()

in Query Analyzer I get an error

Line 1: Incorrect syntax near ')'.

What's going on?

steve

Steve no function beer well without

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-23 : 09:41:20

You can't call an SP with a function as parameter
declare @d datetime
select @d = getdate()
exec sproc_RMSReferrals @d, @d

If you want it to default then

CREATE PROCEDURE sproc_RMSReferrals
@StartMonth datetime = null,
@EndMonth datetime = null
AS
select @StartMonth = coalesce(@StartMonth ,getdate()) ,
@EndMonth = coalesce(@EndMonth ,getdate()) ,

...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-12-23 : 09:42:07

Steve

Get around the constants by declaring the parameters with NULL defaults, then assigning them the GETDATE() default in the stored proc.

Try this

CREATE PROCEDURE sproc_RMSReferrals
(
@StartMonth datetime = NULL,
@EndMonth datetime = NULL
)
AS
BEGIN
SELECT @StartMonth = ISNULL(@StartMonth, GETDATE())
SELECT @EndMonth = ISNULL(@EndMonth, GETDATE())
...

Then to call it with defaults used, try

EXEC sproc_RMSReferrals

Raymond

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-24 : 00:29:47

You could also:


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = GETDATE()
SET @EndDate = GETDATE()

EXEC sproc_RMSReferrals @StartDate, @EndDate

Owais

We make a living out of what we get, but we make a life out of what we give.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources