How to Dynamically Select rows in SQL Server

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

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *