Its probably been driving you crazy too – how to control the number of results a stored procedure returns. Lets say you want to use a function to call a stored procedure and specify the number of results returned, like the TOP 10, or TOP 100 – there are many reasons why this is useful.
In my case I needed to show the TOP 10 last accessed records on the home page of a system, but having a “Show more” link that then showed more. I have set a default of 10 results, but obviously the system can pass more in to the stored procedure and overwrite this default value.
Here is the solution! Its all about the parenthesis around the parameter!!
CREATE PROCEDURE class_Client_ListTop10LastUpdated @NumberToShow int = 10 AS BEGIN SET NOCOUNT ON; SELECT Top (@NumberToShow) * FROM Clients ORDER BY LastUpdated DESC END