Using Conditional Parameters in SQL Server

Most dynamic sites have a database search of some variety. Stored Procedures are much more desirable in a web application for many reasons, yet when it comes to performing searches, the stored procedure is not as easily implemented. In a scripted web application, you can create dynamic SQL statements on the fly, putting conditional logic into your WHERE clauses and making the SQL statement fit the situtation. In a stored procedure, it's not so easy. This article will show one way to pass parameters conditionally to your SQL stored procedure. The technique applies to SQL Server, with code provided for ASP and ColdFusion.

The article assumes you know how to create stored procedures, work with the Query Analyzer, and set up your own DSNs. The download at the end of the article contains the code for the ASP and ColdFusion pages, as well as the SQL code to generate the table and stored procedures.

Conditional Where Clause

In a web application, dynamic SQL statements are used all the time. Picture a blog application that allows you to list all blog entries, blog entries for one category, or blog entries for one blogger. This is 3 different situtations that demand 3 different SQL statements. Using web scripting, you might do it like this:

ColdFusion

<cfset sql = "">
<cfquery name="rsBlogs" datasource="#mydsn#">
SELECT * FROM Blogs WHERE 1=1
<cfif isdefined("url.Category")>
AND Category = '#url.Category#'
</cfif>
<cfif isdefined("url.Blogger")>
AND Blogger = '#url.Blogger#'
</cfif>
</cfquery>

ASP/VBScript

sql = "SELECT * FROM Blogs WHERE 1=1 "
If Request("Category") <> "" Then
sql = sql & " AND Category = '" & Request.Category & "'"
End If
If Request("Blogger") <> "" Then
sql = sql & " AND Blogger = '" & Request.Blogger& "'"
End If
Set rsBlog_conn = Server.CreateObject("ADODB.Connection")
rsBlog_conn.Open MM_Blog_STRING
Set rsBlog = Server.CreateObject("ADODB.Recordset")
rsBlog.Open sql, rsBlog_conn, 1, 1

Conditional WHERE clauses in SQL Server stored procedures are not the easiest thing to accomplish. You cannot simply do this, for example:

-- **BAD CODE DOES NOT WORK**
SELECT * FROM blogs
IF @catogory IS NOT NULL BEGIN
WHERE Category = @category
END
IF @blogger IS NOT NULL BEGIN
WHERE blogger = @blogger
END

This is typically how you would script some logic in ASP or ColdFusion, however it will not work using the SQL language. One possible solution is to create a string and use dynamic SQL:

DECLARE @sql varchar(1024)
SET @sql = 'SELECT * FROM Blogs '
IF @category IS NOT NULL BEGIN
SET @sql = @sql + ' WHERE category = ' + @category
END
IF @blogger IS NOT NULL BEGIN
SET @sql = @sql + ' WHERE blogger = ' + @blogger
END
EXEC(@sql)

However, when you do that you also lose the advantage of using stored procedures: your code is not compiled. The solution I'm going to talk about is to use the ISNULL function. First, I'll set up a sample table for SQL Server so that you can try the technique. Run the following script in the SQL Server Query Analyzer to create and populate the sample table:

CREATE TABLE Blogs (
BlogID int IDENTITY (1, 1) NOT NULL ,
BlogItem varchar (1024) NULL ,
Category varchar (50) NULL ,
Blogger varchar (50) NULL ,
BlogItemDate datetime NULL
)
GO

ALTER TABLE Blogs ADD
CONSTRAINT DF_Blogs_BlogItemDate
DEFAULT (getdate()) FOR BlogItemDate,
CONSTRAINT PK_Blogs PRIMARY KEY CLUSTERED (BlogID)
GO

INSERT Blogs (BlogItem, Category, Blogger)
VALUES ('Testing entry 1', 'Misc','Tom')
INSERT Blogs (BlogItem, Category, Blogger)
VALUES ('Testing entry 2', 'Web','Jack')
INSERT Blogs (BlogItem, Category, Blogger)
VALUES ('Testing entry 3', 'Web','Tom')
INSERT Blogs (BlogItem, Category, Blogger)
VALUES ('Testing entry 4', 'Database','Frank')
INSERT Blogs (BlogItem, Category, Blogger)
VALUES ('Testing entry 5', 'Database','Frank')

Now, look at the following SQL statement:

SELECT * FROM blogs
WHERE Category LIKE ISNULL(@category,'%')
AND Blogger LIKE ISNULL(@blogger, '%')

The ISNULL function says "If the first value is null, use the second value, else use the first value". For the @category variable, we are saying that if @category is null, use the '%', which is the wildcard for matching all records. The same is true for the @blogger variable -- if it is null, the sql statement uses the '%' wildcard. This makes the statement very flexible. Now, when the @blogger variable is null and @category variable is null, all records are returned. If you pass either variable, it is used in the WHERE clause. This can be done with many levels of conditional WHERE clause logic, although only two values are shown here.

Creating the Stored Procedure

The stored procedure uses the code from the SQL statement above. Of course, for this technique to work, you also have to give your variables default values of null in the declaration of the stored proc:

CREATE PROCEDURE spGetBlogs
@category varchar(255) = null,
@blogger varchar(255) = null

AS

SELECT *
FROM Blogs
WHERE Category LIKE ISNULL(@category,'%')
AND Blogger LIKE ISNULL(@blogger, '%')
ORDER BY BlogItemDate desc

To run this, you can try the Query Analyzer again. The following will return all rows:

exec spGetBlogs

The following will return all rows where the Category is 'Database'

exec spGetBlogs 'Database'

The following will return all rows with the Blogger is equal to 'Frank'

exec spGetBlogs null,'Frank'

And finally, the following will return rows where the category is 'Web' and the Blogger is 'Tom'

exec spGetBlogs 'Web','Tom'

Using the Stored Proc in the Web Application

For this to work in your web application, you need to pass null values where no values exist. This is easy in ColdFusion with a simple <cfquery> tag, or a <cfstoredproc> tag:

<cfquery name="rsBlogs" datasource="#mydsn#">
exec spGetBlogs
<cfif isdefined("url.Category")>
  '#url.Category#'
<cfelse>
  null
</cfif> ,
<cfif isdefined("url.Blogger")>
  '#url.Blogger#'
<cfelse>
  null
</cfif>
</cfquery>

Using <cfstoredproc>, you could do this:

Tip: The <cfprocparam> tag is great for passing parameters to stored procedures, because it will make the parameter more safe against SQL injection attacks, as well as allowing you to pass NULL values using the null attribute. The null attribute takes a boolean value, so an expression evaluation can be used as in the code above (null="#url.Category EQ ''#").

An ASP stored procedure call might look like this:

<%
' Set up two variables, and make the default value NULL
Dim Command1__category
Command1__category = NULL
if(Request("category") <> "") then Command1__category = Request("category")

Dim Command1__blogger
Command1__blogger = NULL
if(Request("blogger") <> "") then Command1__blogger = Request("blogger")

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_connBlog_STRING
Command1.CommandText = "dbo.spGetBlogs"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@category", 200, 1,50,Command1__category)
Command1.Parameters.Append Command1.CreateParameter("@blogger", 200, 1,50,Command1__blogger)
set rsBlog = Command1.Execute
rsBlog_numRows = 0

%>

Conclusion

Using SQL is straightforward until you want to do something dynamic with it. When that happens, the complexities of the language sometimes make simple things like dynamic WHERE clauses very hard to accomplish. The article showed one way around the issue.