problem with this stored 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 2005 Forums

Transact-SQL (2005)

problem with this stored procedure.

Author

Topic

ItzRayz
Starting Member

1 Post

Posted - 2008-03-22 : 16:27:59

Hi all. I'm having some problem with this stored procedure.

create procedure dbo.p_add_visit_improved
@visit_member_id int,
@visit_hh_id int,
@visit_datetime datetime
as

INSERT INTO foodpantry_household_visits (visit_member_id, visit_hh_id, visit_datetime)

values (@visit_member_id, @visit_hh_id, @visit_datetime)

if @visit_datetime = null
set @visit_datetime = getdate()

I've been trying to make it work so that the SP will automatically use the getdate() function if a date is not provided by the user.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-22 : 17:47:49

Should you check @visit_datetime before inserting?

mfemenel
Professor Frink

1421 Posts

Posted - 2008-03-23 : 05:36:09

default your @visit_date time parameter to be getdate(). If a user passes a date it will be used otherwise it will fall throught to the system date/time.

Mike
"oh, that monkey is going to pay"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-23 : 11:40:55

YOu can declare getdate() as the default value in the CREATE of the sproc
(@visit_date datetime = null) or in your sproc, change your code a little
if @visit_date is null set @visit_Date = getdate()

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-23 : 12:06:22

or @visit_date=isnull(@visit_date,getdate())

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-23 : 21:50:19

Like this (this information is also in Books OnLine under "Create Procedure")...

create procedure dbo.p_add_visit_improved 
@visit_member_id int,
@visit_hh_id int,
@visit_datetime datetime = GETDATE()
as

INSERT INTO foodpantry_household_visits (visit_member_id, visit_hh_id, visit_datetime)

values (@visit_member_id, @visit_hh_id, @visit_datetime)

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-24 : 13:07:16

I don't think you can set a default date value with GETDATE on a stored procedure paramter. Instead you wouldhave to do as suggested above and default the paramter to NULL then check for NULL.

create procedure dbo.p_add_visit_improved 
@visit_member_id int,
@visit_hh_id int,
@visit_datetime datetime = NULL
as

INSERT INTO foodpantry_household_visits (visit_member_id, visit_hh_id, visit_datetime)
VALUES (@visit_member_id, @visit_hh_id, COALESCE(@visit_datetime, CURRENT_TIMESTAMP))

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-24 : 16:41:51

quote:
Originally posted by Lamprey

I don't think you can set a default date value with GETDATE on a stored procedure paramter. Instead you wouldhave to do as suggested above and default the paramter to NULL then check for NULL.
create procedure dbo.p_add_visit_improved
@visit_member_id int,
@visit_hh_id int,
@visit_datetime datetime = NULL
as

INSERT INTO foodpantry_household_visits (visit_member_id, visit_hh_id, visit_datetime)
VALUES (@visit_member_id, @visit_hh_id, COALESCE(@visit_datetime, CURRENT_TIMESTAMP))

Ack... you're right. I forgot about that. Thank you for the correction.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources