null valued top() parameter in SQL Server stored proc

I have a generic stored proc in my SQL Server 2005 DB

It returns list of product items for use on a website and I want to make it more reusable across the site so I can use it to return specific quantities of products.

The stored proc takes a num items parameter to limit the amount of items returned but I'm wondering if there is a way I can have the stored proc accepting a null valued parameter with out having to resort to maintaining two select statements or 2 stored procs.

Here's a stripped down example with out the extra stuff in the WHERE clause etc..

create procedure GET_PRODUCTS
   @num_items int = null
as
   select TOP(@num_items) prod_id, prod_name, prod_price 
   from products

end

The question is how do I select all the records as if the top clause was not there in the event null is passed to the proc.

I have a couple of ideas but I'm not sure how they size up in terms of performance with regard to SQL Server and this what I'm trying t开发者_如何学Pythono assess?

1) I could check if the param is null and if so reassign it to a very large integer that should be more than the amount of records in the table (currently about 5000 and growing)

2) Same as above but each time counting the size of the products table each time

---

use an IF and create two queries:

create procedure GET_PRODUCTS

@num_items int = null

as
IF @num_items IS NULL
    select prod_id,prod_name, prod_price from dbo.products
ELSE
    select TOP(@num_items) prod_id,prod_name, prod_price from dbo.products

GO

also get in the habit of always using the table's schema (dbo or whatever), it provides for a better reuse of a query plans.

---

I think you are over complicating it. If you want ALL the rows don't tell it how many to fetch! Just do:

IF @Num_Items IS NULL
BEGIN
   SELECT prod_id,prod_name, prod_price from products
END
ELSE
<Your existing code>

EDIT:

If you absolutely want to use a TOP clause, just assign it to a very high value like 2147483647. This is the highest possible value for the int datatype. The query analyzer won't try to return that many rows, but it will make sure it doesn't return MORE than you specify, and it shouldn't affect performance at all.

---

you can use a dynamic query:

create procedure GET_PRODUCTS

@num_items int = null

as
declare @sql nvarchar(max)

set @sql = N'select '
if @num_items is not null
    set @sql = @sql + 'TOP(@num_items) '

set @sql = @sql + 'prod_id,prod_name, prod_price from products'

execute sp_executesql @sql
    ,N'@num_items int = null'
    ,@num_items
end

---

You can do

 select TOP(@num_items) prod_id, prod_name, prod_price 
   from products where @num_items is not null
union
 select prod_id, prod_name, prod_price 
   from products where @num_items is null

or you can

select TOP(isnull(@num_items,Very large number)) prod_id, prod_name, prod_price 
   from products