Rusty Divine

Live, Love, Learn, Teach

Tips on Using a Stored Procedure for Searching

Every business application I’ve worked on has the All-Encompasing Search Tool.  Its the one where you have about 30 text, dropdown, and list boxes that the user can enter their search parameters into (First Name, End Date, Product Number, Product Status, etc.).

What do you do when you’re on a project that uses stored procedures to search the database?  Furthermore, many DBA’s don’t allow direct access to the tables or views in the database for security reasons, which means that you can’t even pass in a dynamically-generated SQL string and do an EXEC @SQL on it.  How do you do this complex search using parameters and nont use dynamic SQL?

I’ve broken the issue down into four main problems and their solutions:

Problem:

There are like 120 search parameters that are possible, but most are probably going to be left blank by the user and should therefore should not not narrow the results.  How do I include these null parameters in my SQL without affecting the results?

Solution:

By including an OR clause for every parameter that cancels its affect out if it is null:

 WHERE ([UserId] = @UserId OR @UserId is null) 

Alternatively, force a null value to another meaningful value:

COALESCE(@WithinXDaysofRenewal,0)

Problem:

On the Search Tool, there is an option to search by “OR” or by “AND”, e.g. FirstName = “Johnny” AND LastName = “SMITH”.  How can a stored procedure switch between narrowing results (using AND) and expaning the results (using OR)?

Solution:

The stored procedure will need to have 2 distinct select clauses, one with OR’s in the where clase and one with AND’s.  Pass a parameter (@SearchUsingOr) into the stored procedure, and then set up the branch like:

 IF ISNULL(@SearchUsingOR, 0) <> 1   BEGIN     SELECT …     FROM …     WHERE (FirstName = @FirstName OR @FirstName = Null) AND --use AND   END 
ELSE   BEGIN       SELECT …     FROM …     WHERE (FirstName = @FirstName OR @FirstName = Null) OR --use OR   END 

Problem:

The user can select multiplve values from a multi-select list box for one or more of the parameters; e.g. there is a list box with all 50 states in it, and the user can select any number of them.

Solution:

Pass the selected values as one comma-delimited list paramter : @States = “NE, WA, CA”, then use the table-valued function “Split” below to split these values out into a result set.  Join on the result set in the FROM clause of the search query like this:

 
SELECT usrs.* 
FROM dbo.Users usrs INNER JOIN [dbo].[fn_Split] ('NE,WA,CA', ',' ) splt ON usrs.State = splt.value 

Here’s the script to create the split function (I think this orignally appeared in an MSDN article):

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Split](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
	@value varchar(8000),
	@bcontinue bit,
	@iStrike smallint,
	@iDelimlength tinyint

IF @sDelim = 'Space'
	BEGIN
	SET @sDelim = ' '
	END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
	BEGIN
	WHILE @bcontinue = 1
		BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
		IF CHARINDEX(@sDelim, @sText)>0
			BEGIN
			SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			
--Trim the element and its delimiter from the front of the string.
			--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
			SET @idx = @idx + 1
			SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
		
			END
		ELSE
			BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			--Exit the WHILE loop.
SET @bcontinue = 0
			END
		END
	END
ELSE
	BEGIN
	WHILE @bcontinue=1
		BEGIN
		--If the delimiter is an empty string, check for remaining text
		--instead of a delimiter. Insert the first character into the
		--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
		IF DATALENGTH(@sText)>1
			BEGIN
			SET @value = SUBSTRING(@sText,1,1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			SET @idx = @idx+1
			SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
			
			END
		ELSE
			BEGIN
			--One character remains.
			--Insert the character, and exit the WHILE loop.
			INSERT @retArray (idx, value)
			VALUES (@idx, @sText)
			SET @bcontinue = 0	
			END
	END

END

RETURN
END

Problem:

I need to page my result set and don’t want to store the entire result set in memory (in a dataset or something).

Solution:

SQL 2005 has introduced a Row_Number() function that works like this:

select ROW_NUMBER () OVER ( order by AccountName asc) as rownum, accountname from dbo.account

To see more, check out this page.

For SQL 2000, one way I like to do this is to set the rowcount in the SQL stored proc.  I think this page has an example of how i’ve done it in the past.  SQL 2005 may have some better features for this; I haven’t looked into it.

Problem:

I need to page my result set alphabetically, like # A-D E-H etc..

Solution:

You can use a LIKE statement with [Brackets] to get the results you want:

SELECT AccountName FROM dbo.Account WHERE AccountName LIKE '[ABC]%' 

SELECT AccountName FROM dbo.Account WHERE AccountName LIKE '[0123456789!@#$%^&*()]%'  with results as ( 
select  ROW_NUMBER () OVER ( order by AccountName asc) as rownum,  accountname from  dbo.account where  accountname like '[ABC]%' ) 
select * from results where rownum between 10 and 15 
blog comments powered by Disqus