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

Hire Me

Follow me on Facebook

Follow me

Do you find this Blog helpful?

Follow by Email

About Me

My Photo
Expert Senior Software Developer

Microsoft Business Card