setting a STORED PROCEDURE a default time value

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

SQL Server Development (2000)

setting a STORED PROCEDURE a default time value

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-29 : 04:08:08

i want in a SP when i dont pass to a variable of type DATETIME a value then iit will get the default GETDATE()
i tried to set the recived variables to :
@CUrrentDate=GETDATE()
and it isnt work why?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-11-29 : 04:15:36

Make the default Null and then the first step in the proc to be:
SET @CurrentDate = COALESCE(@CurrentDate, GETDATE())

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 04:18:04

It's because wether or not the function is deterministic.

Try this

CREATE PROCEDURE a
(
 @Param1 DATETIME = NULL
)
AS

SET NOCOUNT ON

IF @Param1 IS NULL
  SET @Param1 = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

SET @Param2 = ISNULL(@Param2, GETDATE())

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-11-29 : 05:14:27

ok thnaks alot

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-29 : 09:50:01

Peter, what do you mean by deterministic?

CREATE PROCEDURE myProcedure ( 
  @Param1 DATETIME = GETDATE()
)
AS
BEGIN

  SELECT @Param1

END 
GO

EXEC myProcedure                                 --> '2006-11-29 15:48:02.125'
EXEC myProcedure @Param1 = '2001-01-05 12:00:00' --> '2001-01-05 12:00:00.000'

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 16:31:38

Great!
It works for SP but not for FUNCTION. Have to remember that.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-29 : 16:41:19

hmm... when i try to run this i get:
Msg 102, Level 15, State 1, Procedure myProcedure, Line 3
Incorrect syntax near '('.

i also like to use this for default values


CREATE PROCEDURE myProcedure ( 
  @from DATETIME = '18000101'
  @to DATETIME = ''99991231''
)
AS

select ...
where somedatecolumn between @from and @to

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 16:47:23

Lumbago, are you running this on SQL 2005?

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-29 : 17:10:24

it fails there too for me.

any special connection options?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 17:15:01

Well, I have SQL 2000 on my laptop and it doesn't work here with = GETDATE().
But it works with = '20061031'

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-29 : 17:54:34

quote:
Originally posted by spirit1

hmm... when i try to run this i get:
Msg 102, Level 15, State 1, Procedure myProcedure, Line 3
Incorrect syntax near '('.

i also like to use this for default values

CREATE PROCEDURE myProcedure (
@from DATETIME = '18000101'
@to DATETIME = ''99991231''
)
AS

select ...
where somedatecolumn between @from and @to

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

You have at least 3 syntax errors in that code, so thst would account for the "Incorrect syntax" message.

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-29 : 18:22:37

quote:
Originally posted by Lumbago

Peter, what do you mean by deterministic?
CREATE PROCEDURE myProcedure (
@Param1 DATETIME = GETDATE()
)
AS
BEGIN

SELECT @Param1

END
GO

EXEC myProcedure --> '2006-11-29 15:48:02.125'
EXEC myProcedure @Param1 = '2001-01-05 12:00:00' --> '2001-01-05 12:00:00.000'

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

I don't think this is valid:

@Param1 DATETIME = GETDATE()

From SQL Server 2000 BOL:
"default

Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL."

From SQL Server 2005 BOL:
"default
Is a default value for the parameter. If a default value is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL."

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-30 : 02:27:22

I stand corrected!!

I'm sorry about this but I never actually tried my own code because I have been using similar syntax "forever", but maybe not with GETDATE(). I just tried it now specifying @Param1 varchar(20) = 'Lumbago' and that worked just fine but now I get the part about deterministic.

Sorry...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-30 : 05:08:07

MVJ:
I was reffering to lumbago's code not mine.
mine is just a pseudo code exaple

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-30 : 08:34:42

quote:
Originally posted by spirit1

MVJ:
I was reffering to lumbago's code not mine.
mine is just a pseudo code exaple

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

Well. I was confused too, because I thought I was replying to the OP.

CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-30 : 10:07:17

we'll blame that on too many beers last night

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-02 : 15:24:03

well it isnt possible to init DATETIME @param in the recived values but just init it to null and later give it the curent time

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources