Return specific number of rows in result set from Stored Procedure-CodePudding

When we make a stored procedure call we pass input parameter of how many rows we want to get from result. Also, we want specific columns returned which is obtained through join operation on tables.

My doubt is can we return the result as table but if in that approach how to limit result rows to specific count which is passed as input parameter.

I also searched and found about using Fetch next rows only but can we use that without offset logic.

Can somebody suggest me if there is any better approach than above mentioned?

CodePudding user response：

Here is an example of how you could use TOP.

create or alter procedure TopTest
(
    @RowCount int
) as
    select top (@RowCount) *
    from sys.columns c
    order by c.name

And here is how you could do this using OFFSET/FETCH

create or alter procedure TopTestOffset
(
    @RowCount int
) as    
    select  *
    from sys.columns c
    order by c.name
    offset 0 rows
    fetch first (@RowCount) rows only

CodePudding user response：

TOP and OFFSET are easier to use if you need to get first n rows. If you need a range of rows (i.e. for paging), you can use CTE

            with vw as (
                SELECT ROW_NUMBER() OVER (ORDER BY column1) AS RowNumber, 
                    columnlist 
                from YourTable      
            ) select * from vw
                where RowNumber between 1 and @NumberOfRows