SP Default Values - 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)

SP Default Values

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-06 : 09:54:36

I'm trying to design the SP below and have the DueDate default as today plus 14 if not supplied. Is it possible to do something like this? It doesn't seem to like the getDate() function.

Thanks
Ryan

CREATE PROCEDURE [usp_TE_addWorkItem]

(
@ItemType VARCHAR(20),
@ItemDescription VARCHAR(250),
@MAC INT = NULL,
@AccountNum VARCHAR(13) = NULL,
@Referral_ID INT = NULL,
@AssignTo VARCHAR(6),
@DueDate DATETIME = DATEADD(day, 14, getDate())
)

AS

INSERT INTO TEWorkList(ItemType,ItemDescription,MAC,AccountNum,Referral_ID,AssignTo)
VALUES(@ItemType,@ItemDescription,@MAC,@AccountNum,@Referral_ID,@AssignTo)

Ryan Everhart
SBC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 10:15:14

no it doesn't like it

use after insert trigger for this.

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

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-10-06 : 13:53:55

Where the heck do you reference @DueDate ?!?! Anywho, I'd suggest:

CREATE PROCEDURE [usp_TE_addWorkItem] (
   @ItemType VARCHAR(20)
,  @ItemDescription VARCHAR(250)
,  @MAC INT = NULL
,  @AccountNum VARCHAR(13) = NULL
,  @Referral_ID INT = NULL
,  @AssignTo VARCHAR(6)
,  @DueDate DATETIME = NULL
)  AS

SET @DueDate = Coalesce(@DueDate, DATEADD(day, 14, getDate()))

INSERT INTO TEWorkList (
   ItemType, ItemDescription, MAC
,  AccountNum, Referral_ID, AssignTo
) VALUES (
   @ItemType, @ItemDescription, @MAC
,  @AccountNum, @Referral_ID, @AssignTo
)

RETURN

-PatP

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-06 : 14:05:26

Sorry Pat, I didn't put my newest version of the code in. DueDate was going to be inserted into that table below the variable definitions. I'll give your suggestion a shot. What I was trying to do was if @DueDate wasn't sent to the SP than it would automatically get a due date of 2 weeks from now.

Ryan Everhart
SBC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 14:11:52

use this at the begining of the sProc
...
as

if @DueDate is null
begin
set @DueDate = DATEADD(day, 14, getDate())
end

...

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources