Stored Procedure? 1

[woyler]

Hi all,
I have an issue I need a little advice on. What I have is:
a table MY_TABLE, with fields; ID, DATE, PIECES
ID is the key. What I need to work out is, ID being the key, what is the best way to allow incremental updates to the PIECES field if the ID already exists and the date is different in the table.
For Example:

When new data comes in, if the ID is in the database, I need to check to see if the date coming in is different than the date in the existing record, if it is I need to increment the PIECES field, otherwise raise an error.

Existing Data In Table
ID DATE PIECES
10 2/2/2001 10

Incoming Data
10, 2/4/2001, 15

Final Result in Table
ID DATE PIECES
10 2/2/2001 35

I appreciate any help. Thanks,
Bill

 

[rajeevnandanmishra]

Hi Bill,
Create a procedure something like this, and pass your insert values to it:
CREATE PROCEDURE myProc
(@id int, @date datetime, @pieces int) AS
IF EXISTS (SELECT * FROM myTable WHERE id=@id AND date<>@date)
UPDATE myTable
SET pieces=pieces+@pieces
WHERE id=@id
ELSE
BEGIN
raiserror 99997 'Error'
return 99997
END

It will give you an idea.

 

[woyler]

Thanks,
That will get me going. I'm just a VB guy stuck in a DBA world right now. Thanks again for your response.
Bill

 

[woyler]

Ok, one more.
Is there a way to add time intervals(not time of day) in the format of hh:mm:ss?
i.e.
01:20:10 + 2:30 = 01:22:40

Thanks again.
Bill

 

[rajeevnandanmishra]

Hi Bill,
You have to do it in multiple steps. Like:
DATEADD(hh,myHours,myDate) --- To add hours
DATEADD(mi,myMinutes,myDate) --- To add minutes
DATEADD(ss,mySeconds,myDate) --- To add seconds

And if you convert your addible time in seconds then for your example (01:20:10 + 2:30 = 01:22:40) can be achived as
DATEADD(ss,150,myDate) -- 2m 30s means 150 seconds