I want to increment and entry by one on Insert (through stored proc) but I am not sure how to do it.
CREATE PROCEDURE AddLogEntry
@iEntryID int OUPUT,
@iInitiated datetime OUTPUT,
@iLogNumber int OUTPUT
-- Get the next log number
SELECT @iLogNumber = A.LogNumber
FROM (SELECT TOP 1 LogNumber FROM tbErrorLog WHERE fkAreaID = @iArea ORDER BY LogNumber DESC) A
-- Ensure log number is value
IF(@iLogNumber = NULL OR @iLogNumber <= 0)
SET @iLogNumber = 1
-- Add new log entry
INSERT INTO tbErrorLog
(fkAreaID, LogNumber, Initiated)
(@iAreaID, @iLogNumber, @dtInitiated)
IF(@@ERROR = 0 AND @@ROWCOUNT > 0)
SELECT @iEntryID = @@IDENTITY
RAISERROR('Failed to add new log entry!',16,1)
I know the above code is incorrect, but I have no clue how to accomplish this.
Is there anyone that can provide a hint/suggestion?
Acutally, I think the above will work. Any comments?