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.
Create Procedure Search @ProductName VarChar(100) AS Declare @SQL VarChar(1000) Select @SQL = 'SELECT * FROM Products ' Select @SQL = @SQL + 'WHERE ProductID in (' + @ProductName +')' Exec ( @SQL)
Create Procedure Search @ProductName varchar(100) = null As Set @ProductName = isnull(@ProductNmae, '%') Select * from Products where ProductName like @ProductName