SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 setting a STORED PROCEDURE a default time value		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pelegk2
Aged Yak Warrior

Israel
720 Posts
Posted - 11/29/2006 :  04:08:08  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1417 Posts
Posted - 11/29/2006 :  04:15:36  Show Profile  Visit ditch's Homepage  Reply with Quote
Make the default Null and then the first step in the proc to be:
SET @CurrentDate = COALESCE(@CurrentDate, GETDATE())


Duane.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 11/29/2006 :  04:18:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

pelegk2
Aged Yak Warrior

Israel
720 Posts
Posted - 11/29/2006 :  05:14:27  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
ok thnaks alot

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

Lumbago
Norsk Yak Master

Norway
3241 Posts
Posted - 11/29/2006 :  09:50:01  Show Profile  Reply with Quote
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"
Edited by - Lumbago on 11/29/2006 09:51:12
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 11/29/2006 :  16:31:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Great!
It works for SP but not for FUNCTION. Have to remember that.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 11/29/2006 :  16:41:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 11/29/2006 :  16:47:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Lumbago, are you running this on SQL 2005?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 11/29/2006 :  17:10:24  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 11/29/2006 :  17:15:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 11/29/2006 :  17:54:34  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 11/29/2006 :  18:22:37  Show Profile  Reply with Quote
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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3241 Posts
Posted - 11/30/2006 :  02:27:22  Show Profile  Reply with Quote
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"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 11/30/2006 :  05:08:07  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 11/30/2006 :  08:34:42  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 11/30/2006 :  10:07:17  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

pelegk2
Aged Yak Warrior

Israel
720 Posts
Posted - 12/02/2006 :  15:24:03  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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 -:)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000