August 10, 2009 at 2:51 pm
i am trying to a stored procedure to update the month and year
Table contains two columns
Month, year
i have one row in the table to start with
07 2009
My stored procedure should do a select on the table and if the month is >=1 and <12 then add 1 to it and update to the table(in this example as 08)
for the year if the month is 12 then reset the month = 1 and year to add 1 to it
How do i do i in a stored procedure?
August 10, 2009 at 2:54 pm
What have you tried so far?
[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 10, 2009 at 2:55 pm
I don't think this does what you want but it does what you asked for and it will be able to get you started at least.
CREATE TABLE #temp (Mon INT, Yr INT)
INSERT INTO #temp VALUES (7, 2009)
INSERT INTO #temp VALUES (12,2009)
SELECT * FROM #temp
UPDATE #temp SET Mon = CASE WHEN Mon >= 1 AND Mon <12 THEN Mon+1 ELSE 1 END,
Yr = CASE WHEN Mon=12 THEN Yr+1 ELSE Yr END
SELECT * FROM #temp
August 10, 2009 at 2:56 pm
Are you inserting a new row or updating the existing row (ie there is only one row in the table)?
August 10, 2009 at 3:05 pm
I'd probably use the built-in date functions, like dateadd. They've been sufficiently tested that there should be no edge cases left.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2009 at 3:36 pm
Or something like this. Uploaded as an attachment as I am having problems posting it directly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply