Dynamic searches without dynamic sql

Search functionality is a feature that comes up with many applications.  I have written more than one search stored procedure (and screen), but I usually ended up with writing dynamic sql (building a SQL statement and using the exec command, see Figure 1 below).  The main disadvantage to dynamic sql statements is that they are not compiled and performance always matters.

I was reading CoDe magazine one day and noticed a part of an article that mentioned dynamic sql without dynamic sql.  I read it and was quite pleased with the approach.  So the basic approach is to have all the lookup parameters as optional parameters, set null parameters to the % wildcard, use a series of like statements in the where clause.

See figure 2 for an example of the sql code.

Figure 1

Create Procedure Search
    @ProductName VarChar(100)

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @ProductName +')'

Exec ( @SQL)

Figure 2
Create Procedure Search
@ProductName varchar(100) = null
Set @ProductName = isnull(@ProductNmae, '%')

Select * from Products
where ProductName like @ProductName

Got something to add?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s