Advice needed on treating null criteria

Question

Advice needed on treating null criteria

Mark Ward

SSC Enthusiast

Points: 124
More actions
September 22, 2004 at 4:47 am

#59612

Hi.
I have a html form that allows the end user to enter optional search criteria (of many types). When this form is submitted I call a stored procedure to return the set of matching records.
My question revolves around the best way to deal with the fields that the end user has left blank. I have two approaches which are given below. I would very much appreciate people's opinions on which of the two approaches is the better. If anybody has a more efficient approach then that would be great - however, I am willing to allow modest inefficiencies for the sake of code maintenance.
Anyway, here are the two approaches. For clarity I have restricted the stored procedure to only take 2 parameters and to also only query on one table.
Approach 1 (Using IsNull)
CREATE Procedure sp1 (@piParam1 int = NULL, @piParam2 int = NULL)
AS
SELECT tbSearchTable.column3
FROM dbo.tbSearchTable
WHERE
tbSearchTable.column1 = IsNull(@piParam1, tbSearchTable.column1)
AND
tbSearchTable.column2 = IsNull(@piParam2, tbSearchTable.column2)
GO
Approach 2 (Dynamically build query)
CREATE Procedure sp2 (@piParam1 int = NULL, @piParam2 int = NULL)
AS
DECLARE @lsSQL varchar(1000)
SET @lsSQL = 'SELECT tbSearchTable.column3 FROM dbo.tbSearchTable '
DECLARE @lsWhereClause varchar(500)
SET @lsWhereClause = ''
DECLARE @lbNeedAndString bit
SET @lbNeedAndString = 0
DECLARE @lsAndString varchar(5)
SET @lsAndString = ' AND '
IF @piParam1 IS NOT NULL
BEGIN
SET @lsWhereClause = ' tbSearchTable.column1 = ' + CAST(@piParam1 AS VARCHAR(10))
SET @lbNeedAndString = 1
END
IF @piParam2 IS NOT NULL
BEGIN
IF 1 = @lbNeedAndString
BEGIN
SET @lsWhereClause = @lsWhereClause + lsAndString
END
ELSE BEGIN
SET @lbNeedAndString = 1
END
SET @lsWhereClause = ' tbSearchTable.column2 = ' + CAST(@piParam2 AS VARCHAR(10))
END
IF 1 < LEN(@lsWhereClause)
BEGIN
SET @lsSQL = @lsSQL + ' WHERE ' + @lsWhereClause
END
EXEC(@lsSQL)
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 1

In such a case, I always use the 1st approach. You can also use COALESCE(...) which returns the first non-null expression instead of IsNull(...)

It is definitely better than using the dynamic approach in terms of efficiency and maintenance

HTH...

Regards,
Sachin Dedhia

Mark Ward SSC Enthusiast Points: 124 More actions · Answer 2

Thanks sachin.

I was hoping that the 1st approach would be the preferred one as I have already written a number of stored procedures that use it. I only came across the 2nd approach recently. Beginners luck, heh.

Thanks again.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Agreed.

2nd approach isn't even specific for nullhandling, it's more generic to resort to dynaminc SQL - which you should always try to avoid!

Dynamic SQL is a beast in itself, and carries loads of consequences that one might not be aware of at first glance.

Always work hard never to use dynamic SQL as long as possible.

/Kenneth