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'
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 (
, website )
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
WHEN 'companyName' THEN companyName
WHEN 'cityName' THEN mailingCity
WHEN 'provinceName' THEN mailingProvince