T-SQL Code: Searching for a Finite Set of Unknowns with a Single SELECT Statement; the ISNULL() Function

/* Use this technique when enabling full-text search functionality is not possible. If full-text search functionality is possible consider a design featuring the CONTAINS Predicate. */

CREATE PROCEDURE ListSSKB ( @subjectID varchar(32) = NULL , @title varchar(255) = NULL , @kbID varchar(32) = NULL ) AS

SET NOCOUNT ON

SELECT subject AS 'Subject' , title AS 'Title' FROM tblKB , tlkpKBSubjects WHERE tblKB.subjectID = tlkpKBSubjects.subjectID AND ISNULL(CONVERT(varchar(32),tblKB.subjectID),'') LIKE ISNULL(@subjectID,'%') AND ISNULL(CONVERT(varchar(32),tblKB.kbID),'') LIKE ISNULL(@kbID,'%') AND ISNULL(title,'') LIKE ISNULL(@title,'%') ORDER BY subject , title

mod date: 2001-03-31T17:08:21.000Z