CODEWITHSUNDEEP
sqlsql-server-2008
Jyothsna
Jyothsna

Reputation: 5

Return N number of rows based on input sent, if 0 return all rows

I have a requirement to pass the number of rows to be returned as a parameter. i.e from a table i have to return the rows based on input parameter.

If 5 , then show top 5 rows. But in case of 0 is sent, it have to show all the rows.

Create Procedure Get_Employee
    @Input int
As
Begin
    select top @Input * 
    from employee
End

Please help me on this , i cannot depend on the identity column as we may have to delete few rows in between.

Upvotes: 0

Views: 54

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82504

First, using top x without using order by means you are basically selecting x arbitrary records from the table (please note that arbitrary is not the same as random).
For more information, read Aaron Bertrand's Bad habits to kick : relying on undocumented behavior.

Second, the simplest solution would be to replace 0 with the number of rows in the table inside your stored procedure:

Create Procedure Get_Employee
    @Input int
As
Begin
    if @Input <= 0 begin -- Note: handling a negative input like it's zero...
        set @Input = (select count(*) from employee)
    end

    select top (@Input) * 
    from employee
    order by Id -- or whatever column you want use to order by
End

Upvotes: 1

Related Questions