Results 1 to 3 of 3

Thread: Select Top 1

  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Select Top 1

    I want to increment and entry by one on Insert (through stored proc) but I am not sure how to do it.

    Code:
    CREATE PROCEDURE AddLogEntry
    
    @iArea int,
    @iEntryID int OUPUT,
    @iInitiated datetime OUTPUT,
    @iLogNumber int OUTPUT
    
    AS
    
    -- 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)
    VALUES
    (@iAreaID, @iLogNumber, @dtInitiated)
    
    IF(@@ERROR = 0 AND @@ROWCOUNT > 0)
           SELECT @iEntryID = @@IDENTITY
    ELSE
           RAISERROR('Failed to add new log entry!',16,1)
    GO
    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?

    Mike B


    Acutally, I think the above will work. Any comments?
    Last edited by MikeB_2k4; 06-11-04 at 10:15.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    do you mean:

    INSERT INTO tbErrorLog
    (fkAreaID, LogNumber, Initiated)
    VALUES
    (@iAreaID, @iLogNumber+1, @dtInitiated)

    or

    set @iLogNumber = @iLogNumber + 1
    ?

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by Kaiowas
    do you mean:

    INSERT INTO tbErrorLog
    (fkAreaID, LogNumber, Initiated)
    VALUES
    (@iAreaID, @iLogNumber+1, @dtInitiated)

    or

    set @iLogNumber = @iLogNumber + 1
    ?
    SET @iLogNumber because the incremented value has to be returned in the output param.

    The actual problem was not that though, I should have been clear, I did not know, or it didn't occur to me at least, how to select the top LogNumber. I did accomplish this I think.

    Mike B
    Last edited by MikeB_2k4; 06-11-04 at 10:40.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •