SQL Proc Modification 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(minu开发者_如何转开发te,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?

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.

You can use an output parameter to send back the information you need - maybe the total row count so that you can define the number of pages to paginate against? You just add the OUTPUT parameter to the proc declaration:

ALTER PROCEDURE [dbo].[spGetMessages]
    @lastRow int,
    @yourFlagHere BIT OUTPUT

I added BIT to support your binary validation, but you can use several other types. For more info, check Returning Data by Using OUTPUT Parameters. You would then assign the value to the output parameter by calling the proc with

EXEC dbo.spGetMessages @lastRowYouDefined, @yourFlagHere OUTPUT

Your @yourFlagHere var will have the value defined from within the procedure.