SQL Proc Modification Question

Question

ALTER PROCEDURE [dbo].[spGetMessages]
    @lastRow int
AS
BEGIN
    -- Insert statements for procedure here
    DECLARE @StartRow INT,@EndRow INT
    SELECT @StartRow = (@lastRow + 1), @EndRow = (@lastRow + 6)

;WITH cte AS (SELECT ROW_NUMBER() OVER (ORDER BY
        CASE WHEN @sort = 'votes1' THEN m.votes END DESC,
        CASE WHEN @sort = 'votes2' THEN m.votes END ASC,
        CASE WHEN @sort = 'age1' THEN datediff(minute,m.timestamp, getdate()) END ASC,
        CASE WHEN @sort = 'age2' THEN datediff(minute,m.timestamp, getdate()) END DESC
      ) AS rows,
      m.message,
      m.messageId
    FROM
      tblMessages m
    WHERE
      m.deleted != 1
      )
     SELECT * 
     FROM cte WHERE ROWS BETWEEN @StartRow AND @EndRow
    ORDER BY rows
END

So this proc gets me a bunch of messages, but passing in the last row value so I can implement paging, and a 'load more' functionality on the front end.

If the proc returns less than 6 messages, I can disable 'load more' obviously, if it returns 6 messages however, I don't know if there's more in the database, or that those were the last 6 messages.

My thought was that if I passed back another DataTable containing either true or false that represented if there were more than these 6 messages left in the database using the last row id, I could use this flag to enable/disable the 'load more' button. Is this a good idea? If not, what's a better plan?

If so, how would I modify this proc to pass that flag back?

Answer 1

We have several procs like this. The easy way was to add an additional column in the output called "TotalCount" which returned a count of all rows.

In your case it would look like:

;WITH cte AS (SELECT ROW_NUMBER() OVER (ORDER BY
        CASE WHEN @sort = 'votes1' THEN m.votes END DESC,
        CASE WHEN @sort = 'votes2' THEN m.votes END ASC,
        CASE WHEN @sort = 'age1' THEN datediff(minute,m.timestamp, getdate()) END ASC,
        CASE WHEN @sort = 'age2' THEN datediff(minute,m.timestamp, getdate()) END DESC      ) AS rows,
            m.message, m.messageId,
        TotalCount = COUNT(m.Id) OVER ( PARTITION BY NULL)
    FROM tblMessages m
    WHERE m.deleted != 1
)
SELECT *
FROM cte 
WHERE ROWS BETWEEN @StartRow AND @EndRow
ORDER BY rows

I'm assuming the tblMessages has a column called Id. The point is to simply count the unique id's in that table.

We did it this way so we wouldn't have to run 2 queries. Of course, depending on the size of data this could have a possible performance impact. So test both ways.

======
BTW, one that thing comes to mind. You might consider not using prefixes (like "tbl" and "sp") That's a very outdated way of doing things and generally completely unnecessary.