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)
AS
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
As
Set @ProductName = isnull(@ProductNmae, '%')
Select * from Products
where ProductName like @ProductName
posted @ Monday, December 07, 2009 7:54 AM