TSQL: Dynamic value for TOP

Question

I have a need to allow users to query all or some records. Right now I am doing this:

DECLARE @Limit INT = NULL

IF @Limit IS NULL SELECT @Limit = COUNT(ID) FROM vwNotifications

SELECT TOP (@Limit) ROW_NUMBER() OVER(ORDER BY Type, CreatedBy DESC) AS Row, Title
FROM vwNotifications

Is there a way I can do this without using the COUNT query?

Answer 1

you have two options

1 make top something like 2 billion if it is 0 but then again..do you really want to return 2 billion rows in 1 shot?

DECLARE @Limit INT = NULL

SELECT @Limit = COALESCE(@Limit, 200000000)

SELECT TOP (@Limit) ROW_NUMBER() OVER(ORDER BY Type, CreatedBy DESC) AS Row, Title
FROM vwNotifications

or do an if else

DECLARE @Limit INT = NULL

IF @Limit IS NULL

SELECT  ROW_NUMBER() OVER(ORDER BY Type, CreatedBy DESC) AS Row, Title
FROM vwNotifications

else

SELECT TOP (@Limit) ROW_NUMBER() OVER(ORDER BY Type, CreatedBy DESC) AS Row, Title
FROM vwNotifications