August 27, 2008 at 4:46 pm
I'm trying to set a default value for a parameter i have defined in a stored procedure. What i have looks like the following.
alter procedure UPDATEHISTORYCURRENTYEAR
@year varchar(10) = '2008'
as
DELETE IV_LISTPRICEHISTORY FROM IV_LISTPRICEHISTORY where [year] = @year
insert into iv_listpricehistory
select uscatvls_6, @year, max(listprce)
from iv00101 i join iv00105 p on i.itemnmbr = p.itemnmbr
group by i.uscatvls_6
a very simple procedure that will delete current data for the current year from a table and repopulate it, a basic refresh feature. I'm not concerned about the data being lost. The problem i'm having is the year being hard coded to default to 2008, its very sloppy. I've tried changing the code to something like this
alter procedure UPDATEHISTORYCURRENTYEAR
@year varchar(10) = datepart(yyyy,getdate())
....
but this gives me an error when i try to alter the procedure. The error says "Incorrect syntax near '(' : Line 2"
that is the only part i've changed when this error occurs. FYI i am using SQL Server 2000, not 2005.
August 27, 2008 at 4:53 pm
Try it like this:
alter procedure UPDATEHISTORYCURRENTYEAR
@year varchar(10) = NULL
AS
IF @year IS NULL Select @year=datepart(yyyy,getdate())
....
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 27, 2008 at 7:41 pm
I'll try that, looks like it should work.
August 27, 2008 at 7:49 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply