March 12, 2009 at 8:09 am
Hi,
I have written a stored procedure that will, when working, insert a new row into a database with 4 columns, the first column is my devide ID, the second is my Data, the final two columns are times.
The database has the following structure
DEVICE_ID INT
ALARM_TYPE INT
INITIAL_TIME DATETIME
ACKNOWLEDGE_TIME DATETIME
My stored procedure is as follows
ALTER PROCEDURE UPDATE_ALARMS_DECODERS_NEW @DECODER_ID INT, @ALARM_TYPE INT
AS
DECLARE @TEMP_DECODER_ID INT
DECLARE @TEMP_ALARM_TYPE INT
SET @TEMP_DECODER_ID = ISNULL(@DECODER_ID,-1)
SET @TEMP_ALARM_TYPE = ISNULL(@ALARM_TYPE,-1)
IF @TEMP_DECODER_ID < 0
BEGIN
RETURN -1
END
IF @TEMP_ALARM_TYPE < 0
BEGIN
RETURN -2
END
IF NOT EXISTS (SELECT DECODER_ID FROM ALARMS_DECODERS WHERE DECODER_ID = @TEMP_DECODER_ID)
BEGIN
INSERT INTO ALARMS_DECODERS (DECODER_ID, ALARM_TYPE, INITIAL_TIME, ACKNOWLEDGE_TIME )
VALUES (@TEMP_DECODER_ID, @TEMP_ALARM_TYPE, NULL, GETDATE())
END
UPDATE ALARMS_DECODERS
SET INITIAL_TIME = GETDATE(),
ACKNOWLEDGE_TIME = GETDATE()
WHERE DECODER_ID = DECODER_ID
I cant seem to work out why this procedure is modifying each line whilst not adding a new entry
TIA
Tony
March 12, 2009 at 8:13 am
This line, "WHERE DECODER_ID = DECODER_ID" will make it update every row. I think you want the second one to be a variable. Not sure which variable it's supposed to be, the input parameter or the temp ID, but I think it's supposed to be one of those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 13, 2009 at 12:45 pm
Perhaps somewhat tangential - but there are a couple of things that you may want to think about -
1. You may want to think about reusing variables instead of creating more temp variables - for instance, you could perhaps do something like: set @device_id = isnull(@device_id, -1).
2. You may also want to try to minimize the number of IF ELSE blocks in your code - not always possible or worth it depending on the scenarios and ease of code maintenance.
Finally - my best shot at your sproc - test it out; hopefully it works.
IF(@decoder_id IS NULL OR @alarm_type IS NULL)
BEGIN
RETURN CASE WHEN @decoder_id IS NULL THEN -1 ELSE -2 END
END
INSERT INTO ALARMS_DECODERS (DECODER_ID, ALARM_TYPE, INITIAL_TIME, ACKNOWLEDGE_TIME)
SELECT @decoder_id, @alarm_type, NULL, GETDATE()
WHERE NOT EXISTS (select * from ALARMS_DECODERS WHERE DECODER_ID = @decoder_id)
UPDATE ALARMS_DECODERS
SET INITIAL_TIME = GETDATE(),
ACKNOWLEDGE_TIME = GETDATE()
WHERE DECODER_ID = @decoder_id
AND @@ROWCOUNT = 0 -- implies the above INSERT did not take place
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply