Stored procedure to find next and previous row in SQL Server 2005

Right now I have this code to find next and previous rows using SQL Server 2005. intID is the gallery id number using bigint data type:

SQL = "SELECT TOP 1 max(p.galleryID) as previousrec, min(n.galleryID) AS nextrec FROM gallery AS p CROSS JOIN gallery AS n where p.galleryid < '"&intID&"' and n.galleryid > '"&intID&"'"
Set rsRec = Server.CreateObject("ADODB.Recordset")
rsRec.Open sql, Conn
strNext = rsRec("nextrec")
strPrevious = rsRec("previousrec")
rsRec.close
set rsRec = nothing

Problem Number 1:

The newest row will return nulls on the 'next record' because there is none. The oldest row will return nulls because there isn't a 'previous record'. So if either the 'next record' or 'previous record' doesn't exist then it returns nulls for both.

Problem Number 2:

I want to create a stored 开发者_Go百科procedure to call from the DB so intid can just be passed to it

TIA

---

This will yield NULL for previous on the first row, and NULL for next on the last row. Though your ordering seems backwards to me; why is "next" lower than "previous"?

CREATE PROCEDURE dbo.GetGalleryBookends
    @GalleryID INT
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH n AS 
    (
        SELECT galleryID, rn = ROW_NUMBER() 
          OVER (ORDER BY galleryID) 
          FROM dbo.gallery
    )
    SELECT
      previousrec = MAX(nA.galleryID), 
      nextrec     = MIN(nB.galleryID)
    FROM n 
    LEFT OUTER JOIN n AS nA
    ON nA.rn = n.rn - 1
    LEFT OUTER JOIN n AS nB
    ON nB.rn = n.rn + 1
    WHERE n.galleryID = @galleryID;
END
GO

Also, it doesn't make sense to want an empty string instead of NULL. Your ASP code can deal with NULL values just fine, otherwise you'd have to convert the resulting integers to strings every time. If you really want this you can say:

      previousrec = COALESCE(CONVERT(VARCHAR(12), MIN(nA.galleryID)), ''), 
      nextrec     = COALESCE(CONVERT(VARCHAR(12), MAX(nB.galleryID)), '')

But this will no longer work well when you move from ASP to ASP.NET because types are much more explicit. Much better to just have the application code be able to deal with, instead of being afraid of, NULL values.

This seems like a lot of work to get the previous and next ID, without retrieving any information about the current ID. Are you implementing paging? If so I highly recommend reviewing this article and this follow-up conversation.

---

Try this (nb not tested)

SELECT TOP 1 max(p.galleryID) as previousrec, min(n.galleryID) AS nextrec 
       FROM gallery AS p 
       CROSS JOIN gallery AS n 
              where (p.galleryid < @intID or p.galleryid is null)
              and (n.galleryid > @intID  or n.galleryid is null)

I'm assuming you validate that intID is an integer before using this code.

As for a stored procedure -- are you asking how to write a stored procedure? If so there are many tutorials which are quite good on the web.

---

Since Hogan contributed with the SQL statement, let me contribute with the stored proc part:

CREATE PROCEDURE spGetNextAndPreviousRecords 
    -- Add the parameters for the stored procedure here
    @intID int 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT TOP 1 max(p.galleryID) as previousrec, min(n.galleryID) AS nextrec 
       FROM gallery AS p 
       CROSS JOIN gallery AS n 
              where (p.galleryid < @intID or p.galleryid is null)
              and (n.galleryid > @intID  or n.galleryid is null)

END

And you call this from code as follows (assuming VB.NET):

 Using c As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
       c.Open()
       Dim command = New SqlCommand("spGetNextAndPreviousRecords")

       command.Parameters.AddWithValue("@intID", yourID)
       Dim reader as SqlDataReader = command.ExecuteReader()
       While(reader.Read())
          ' read the result here
       End While
End Using