Tuesday, June 1, 2010

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


Source: https://www.nilebits.com/blog/2010/04/how-to-dynamically-select-rows-in-sql-server/