Everybody knows that dynamic queries take a lot more of work and time to process. I had last week a case in which I had a variable number of parameters to pass to a stored procedure and one of the parameters was the “order by” clause. Also, I needed to do some pagination on my results and show only 20 rows at a time.

I spent some time researching on the net, reading several blogs about that, and one of the solutions for the dynamic “where clause” came from a blog in Brazil which unfortunately I do not have the address anymore to credit,  and the solution for the order by clause came from SQL Team.

Since I was using MS SQL 2000, I could not make use of the new ROW_NUMBER() function, so I created a temporary table with the passed criteria and selected from it within the range of rows also passed.

Here is the code:

CREATE PROCEDURE dbo.search_companies
/* the optional parameters here */
@startRow int = 1
,    @endRow int = 20
,    @companyID varchar(36) = NULL
,    @companyName nvarchar(50) = NULL
,    @cityName nvarchar(50) = NULL
,    @provinceName nvarchar(50) = NULL
,    @phone varchar(50) = NULL
,    @sortIndex varchar(20) = 'companyName'
AS
BEGIN
SET NOCOUNT ON
/* declare the temporary table */
DECLARE @temp table (
rowNumber int identity
,    companyID varchar(36)
,    companyName nvarchar(50)
,    address1 nvarchar(40)
,    address2 nvarchar(40)
,    cityName nvarchar(30)
,    zipCode varchar(10)
,    province nvarchar(30)
,    country nvarchar(30)
,    phone varchar(25)
,    fax varchar(25)
,    email varchar(80)
,    website varchar(120)
)
/* adjust the parameters if you will use LIKE operator */
if @companyName IS NOT NULL SET @companyName = '%' + @companyName + '%'
if @cityName IS NOT NULL SET @cityName = '%' + @cityName + '%'
if @provinceName IS NOT NULL SET @provinceName = '%' + @provinceName + '%'
if @phone IS not NULL SET @phone = @provinceName + '%'
/* insert into the temp table according to the criteria passed */
INSERT INTO @temp (
companyID
,    companyName
,    address1
,    address2
,    cityName
,    zipCode
,    province
,    country
,    phone
,    fax
,    email
,    website )
SELECT
companyID
,    companyName
,    address1
,    address2
,    cityName
,    zipCode
,    province
,    country
,    phone
,    fax
,    email
,    website
FROM tbl_companies
WHERE (isActive = 1)
AND (companyID = @companyID OR @companyID IS NULL)
AND (companyName LIKE @companyName OR @companyName IS NULL)
AND (cityName LIKE @cityName OR @cityName IS NULL)
AND (province LIKE @provinceName OR @provinceName IS NULL)
AND (a.mailingCountry LIKE @countryName OR @countryName IS NULL)
AND (a.phone LIKE @phone OR @phone IS NULL)
/* select from the temp table according to the range and order by passed */
SELECT * FROM @temp
WHERE rowNumber BETWEEN @startRow AND @endRow
ORDER BY
CASE @sortIndex
WHEN 'companyName' THEN companyName
WHEN 'cityName' THEN mailingCity
WHEN 'provinceName' THEN mailingProvince
END
END