title: How to get previous and next row's value effeciently in SQL server
body: <p>Say I have these rows,</p>
  
  <p>InstrumentID</p>
  
  <pre><code>547
  698
  708
  </code></pre>
  
  <p>InstrumentID is not autogenerated column.</p>
  
  <p>Say if I pass the parameter in procedure as 698, I should get previous value as 547 and next value as 708. How do I do this efficiently in SQL?</p>
  
  <p>I have this procedure but it is not efficient (and not correct).</p>
  
  <pre><code>Alter PROCEDURE GetNextAndPreviousInsturmentID
  (
      @InstrumentID   varchar(14),
      @PreviousInstrumentID   varchar(14) OUT,
      @NextInstrumentID   varchar(14) OUT
  )
  AS
  BEGIN
      Declare @RowNum int = 0
  
      Select @RowNum = ROW_NUMBER() Over (Order by Cast(@InstrumentID as decimal(18))) From Documents Where InstrumentID = @InstrumentID 
  
      ;With normal As
      (   
          Select ROW_NUMBER() Over (Order by Cast(@InstrumentID as decimal(18))) as RowNum, Cast(InstrumentID as decimal(18)) as InstrumentID
              From Documents 
      )
      Select @PreviousInstrumentID = InstrumentID From normal
          Where RowNum = @RowNum - 1
      Select @NextInstrumentID = InstrumentID From normal
          Where RowNum = @RowNum + 1
  
  END
  GO
  </code></pre>