How do I set Default GetDate( ) in Store Procedure

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)

How do I set Default GetDate( ) in Store Procedure

Author

Topic

cutever
Starting Member

32 Posts

Posted - 2002-05-09 : 03:00:30

I try to create a store procedure as following.

CREATE PROCEDURE sp_InsertRecord

@EmpNo varchar(5),
@PersonID int,
@UserID int,
@JoinDate datetime default GETDATE(),

AS

Insert into tbl_Employee1 (EmpNo, PersonID, UserID, JoinDate)
values ( @EmpNo, @PersonID, @UserID, @JoinDate)

It prompt out error.

How do I set Default GetDate( ) in Store Procedure ?

or

exec sp_InsertRecord 'E123','1','1',''
- Run -
I want the @JoinDate set the current date automatically.

Please Help !!

Thank you very much

Ver

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-09 : 03:04:58

You can't

Firstly, you don't use "default" for stored procedure parameters, you just use an equals sign. But you can not use a function there.

You could set it to NULL then check for NULL.

I.e.




CREATE PROCEDURE sp_InsertRecord 

@EmpNo varchar(5), 
@PersonID int, 
@UserID int, 
@JoinDate datetime = NULL 

AS 

If @joindate IS NULL
    SELECT @joindate = GetDate()

Insert into tbl_Employee1 (EmpNo, PersonID, UserID, JoinDate) 
values ( @EmpNo, @PersonID, @UserID, @JoinDate)

Hope that helps

Damian

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-05-09 : 03:16:10

quote:

You can't

Firstly, you don't use "default" for stored procedure parameters, you just use an equals sign. But you can not use a function there.

You could set it to NULL then check for NULL.

I.e.


Just to add: If there's a possibility of the user passing a NULL explicitly, it's better to set the parameter to something like '1900/1/1' instead of NULL. This allows the procedure to search for NULLs as well.


CREATE PROCEDURE sp_InsertRecord 

@EmpNo varchar(5), 
@PersonID int, 
@UserID int, 
@JoinDate datetime = NULL 

AS 

If @joindate IS NULL
    SELECT @joindate = GetDate()

Insert into tbl_Employee1 (EmpNo, PersonID, UserID, JoinDate) 
values ( @EmpNo, @PersonID, @UserID, @JoinDate)

Hope that helps

Damian

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources