How to Dynamically Select rows in SQL Server
If you want to restrict other developers from using your Stored Procedures that returns a huge amount of data which affects server performance and Application Performance weather it is a Windows or Web Application.
You can use the following idea when you create Stored Procedures:
-- ============================================= -- Author: Amr Saafan -- Create date: Jan 5, 2010 -- Description: Get Employees -- ============================================= CREATE PROCEDURE GetEmployees @RowsCount INT = 10 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT TOP (@RowsCount) [EmployeeID], [EmployeeName], [EmployeeEmail] FROM [dbo].[Employee] END GO
As we can see whenever the Stored Procedure called, it will not return more than 10 rows which is a reasonable amount of rows and in the same time if you want less or more you can just pass the number of rows you need @RowsCount = 1 or 1000
