1

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 procedure to call from the DB so intid can just be passed to it

TIA

ThatGuyInIT
  • 2,239
  • 17
  • 20
Patriotec
  • 399
  • 1
  • 6
  • 21
  • 1
    [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection), anyone? – Oded Sep 04 '11 at 17:41
  • @Oded, nah. That's a [myth](http://stackoverflow.com/questions/6786034/can-parameterized-statement-stop-all-sql-injection]). – Ben Sep 04 '11 at 17:45
  • @Ben - what is a myth? The SQL Injection is a very possible with this code? – Oded Sep 04 '11 at 17:48
  • @Oded I know; and with Hogan's answer below. The link goes to a question on why it get's missed so often. – Ben Sep 04 '11 at 17:53
  • We already have an included file to prevent SQL injection. The important thing here right now is to solve the problem, then i'll use ADODB command with parameters...then create the stored procedure. I see more comments about sql injection rather than an answer to the question – Patriotec Sep 04 '11 at 18:17
  • That's why those are comments and not answers... – Aaron Bertrand Sep 05 '11 at 03:03

3 Answers3

1

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • i can understand the next value to be null on the newest record because there isnt a next record, but the prvious is being returned as null also on the first record – Patriotec Sep 04 '11 at 21:08
  • 1
    What value do you want returned for the row previous to the first row? What row comes before the first row? Word problems and guessing aren't fun; ***please*** show sample data and desired results. – Aaron Bertrand Sep 04 '11 at 22:32
0

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.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • same affect with your code the first and last record returns NULL so no ID is given if one isnt found – Patriotec Sep 04 '11 at 17:59
  • @Patriotec: so what do you want to receive in that case? Same result for both, next and previous records? – Icarus Sep 04 '11 at 18:18
  • If one doesn't EXIST set that value to "" and return the other. If both EXIST return both next and previous records. Thanks for the help. – Patriotec Sep 04 '11 at 18:24
  • You can get that result with `MAX(ISNULL(p.galleryID),"")` – Hogan Sep 04 '11 at 19:21
  • Same result. Something wrong with the query in the stored procedure. Also you can't assign "" in SQL if ISNULL. It's not allow. Even if I try to do MAX(ISNULL(p.galleryID),"norec") or MAX(ISNULL(p.galleryID),0). Nothing seems to work – Patriotec Sep 04 '11 at 20:35
  • Sorry @Patriotec -- a string constant uses `''` not double quotes. What is the error you get -- please update the original question. – Hogan Sep 05 '11 at 01:50
0

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
Icarus
  • 63,293
  • 14
  • 100
  • 115