How to Retrieve either Top N rows OR all rows from a table in SQL Server

Question

I have a

datagridview

in which a user is allowed to retriev either

n

records (using textbox) or All records by clicking on a

buttun

Now want to retrieve

Top N

records Or All Records with a single Query.
right now I'm using 2 different Queries to achieve.

//retrieving Top N Records

 SELECT Top @Rows FROM CaseDetails //@Row is parameter

And

 //retrieving All records

SELECT Top (SELECT COUNT(*) FROM CaseDetails) FROM CaseDetails

How can i Use a single query in

SQL Server

to perform these 2 options ?

Answer 1

This is working fine

Declare parameters

Create Procedure uspRetrieve
@Rows int =NULL
AS
BEGIN
   SET NOCOUNT ON;

   SET ROWCOUNT @Rows
   SELECT * FROM CaseDetails
End

IF you supply @Row=0 you will get all records else you get @Row =your limit

Sign up