title: SQL that can pulls records by a possible three values or none at all
body: <p>Say you have <code>WIDGETS</code> table:</p>
  
  <ul>
  <li>WidgetID int</li>
  <li>ColorID int  (Lookup values: Red, Blue, Green, Yellow, Black, Brown)</li>
  <li>SizeID int (Lookup values: Small, Med, Big, Large)</li>
  <li>Weight int (Lookup values: UltraLight, Light, Normal, Heavy, UltraHeavy</li>
  </ul>
  
  <p>Ok that's the general idea of the table. I don't need the lookup names, what I do have is the lookupvalue ID's.  So from this table, I would to be able pull lists back that meet say the following crieria:</p>
  
  <ol>
  <li>Show me all widgets who are Red, or Blue, or Black</li>
  <li>Show me all widgets who are Red or Blue, and Small or Med</li>
  <li>Show me all widgets who are Heavy only</li>
  <li>Show me all widgets who are heavy and Yellow</li>
  <li>Show me all widgets who are heavy and yellow and small or big</li>
  <li>Show me all widgets who are Large only</li>
  <li>Show me all widgets who are Green only</li>
  </ol>
  
  <p>Get the idea? I've been trying to work on a Stored Proc that allowed me to send in some type of parameters. Even tried dynamic sql but getting weird errors. can't remember now. </p>
  
  <p><i>My Attempt #3, sort of works if I could get it working in a Proc and be able to condition out which Join's I want<br /></i></p>
  
  <p>Examples of what I've tried:</p>
  
  <p><strong>Note:</strong> In my examples: (CourseID is WidgetID) and (StateID is ColorID) and (CreditTypeID is SizeID) and (SubjectID is WeightID)</p>
  
  <p><strong>Attempt Method 1</strong></p>
  
  <pre><code>ALTER PROCEDURE [dbo].[CourseListFullInfoByStateCreditSubject]
    @StateIDs VARCHAR(200) = '',
    @CreditTypeIDs VARCHAR(200) = '',
    @SubjectTypeIDs VARCHAR(200) = ''
  AS
  
  BEGIN
  
    DECLARE @SQL AS NVARCHAR(MAX)
    SET @SQL = 'SELECT DISTINCT
                       C.CourseID,
                       LU.FirstName,
                       LU.LastName,
                       (SELECT COUNT(ReviewID) FROM Review AS R WHERE R.CourseID = C.CourseID) AS ReviewCount
                  FROM [Course] AS C WITH(NOLOCK)
                  JOIN LexUser AS LU ON LU.LexUserID = C.PresenterID '
  
  If @StateIDs IS NOT NULL AND @StateIDs &lt;&gt; '''' AND @StateIDs &lt;&gt; '0'
  BEGIN
    SET @SQL = @SQL + ' JOIN CourseToState AS CS ON CS.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CS.StateID AS VARCHAR) + '','', '','' + @StateIDs + '','') &gt; 0 '
  END
  
  If @CreditTypeIDs IS NOT NULL AND @CreditTypeIDs &lt;&gt; '' AND @CreditTypeIDs &lt;&gt; '0'
  BEGIN
    SET @SQL = @SQL + 'JOIN CourseToCreditType As CC ON CC.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CC.CreditTypeID AS VARCHAR) + '','', '','' + @CreditTypeIDs + '','') &gt; 0 '
  END
  
  If @SubjectTypeIDs IS NOT NULL AND @SubjectTypeIDs &lt;&gt; '' AND @SubjectTypeIDs &lt;&gt; '0'
  BEGIN
    SET @SQL = @SQL + 'JOIN CourseToSubject As CSu ON CSu.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CSu.SubjectID AS VARCHAR) + '','', '','' + @SubjectTypeIDs + '','') &gt; 0 '
  END
  
  EXEC sp_executesql @SQL
  </code></pre>
  
  <p>With Attempt 1 I try and send in the ID's: </p>
  
  <pre><code>[CourseListFullInfoByStateCreditSubject] ''1,2,4'', ''0'', ''0''
  </code></pre>
  
  <p>...but I get an error, "Incorrect syntax near '1'."</p>
  
  <p><strong>Attempt Method 2, gives same error</strong></p>
  
  <pre><code>DECLARE @SQL AS NVARCHAR(MAX)
  SET @SQL = 'SELECT DISTINCT
                     C.CourseID,
                     LU.FirstName,
                     LU.LastName,
                     (SELECT COUNT(ReviewID) 
                       FROM Review AS R 
                      WHERE R.CourseID = C.CourseID) AS ReviewCount
               FROM [Course] AS C WITH(NOLOCK)
               JOIN LexUser AS LU ON LU.LexUserID = C.PresenterID '
  
  If @StateIDs IS NOT NULL AND @StateIDs &lt;&gt; '''' AND @StateIDs &lt;&gt; '0'
  BEGIN
    SET @SQL = @SQL + ' JOIN CourseToState AS CS ON CS.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CS.StateID AS VARCHAR) + '','', '','' + @StateIDs + '','') &gt; 0 '
  END
  
  If @CreditTypeIDs IS NOT NULL AND @CreditTypeIDs &lt;&gt; '' AND @CreditTypeIDs &lt;&gt; '0'
  BEGIN
    SET @SQL = @SQL + 'JOIN CourseToCreditType As CC ON CC.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CC.CreditTypeID AS VARCHAR) + '','', '','' + @CreditTypeIDs + '','') &gt; 0'
  END
  
  If @SubjectTypeIDs IS NOT NULL AND @SubjectTypeIDs &lt;&gt; '' AND @SubjectTypeIDs &lt;&gt; '0'
  BEGIN
    SET @SQL = @SQL + 'JOIN CourseToSubject As CSu ON CSu.CourseID = C.CourseID AND CHARINDEX('','' + CAST(CSu.SubjectID AS VARCHAR) + '','', '','' + @SubjectTypeIDs + '','') &gt; 0'
  END
  </code></pre>
  
  <p><strong>Attempt Method 3 - Sort of Works</strong>
  This method only works if I send it ID's for each feld, I can't leave one blank to get "all" and I can't get it working in a stored proc, which is what I tried to do in Attempt #2</p>
  
  <pre><code>DECLARE @StateIDs VARCHAR(200) = ''
  DECLARE @CreditTypeIDs VARCHAR(200) = ''
  DECLARE @SubjectTypeIDs VARCHAR(200) = ''
  SET @StateIDs = '1,3,2,'
  SET @CreditTypeIDs = '1,3'
  SET @SubjectTypeIDs = '1,2,3,4' 
  
  SELECT DISTINCT
         C.CourseID,
         LU.FirstName,
         LU.LastName,
         (SELECT COUNT(ReviewID) FROM Review AS R WHERE R.CourseID = C.CourseID) AS ReviewCount
    FROM [Course] AS C WITH(NOLOCK)
    JOIN LexUser AS LU ON LU.LexUserID = C.PresenterID 
    JOIN CourseToState AS CS ON CS.CourseID = C.CourseID AND CHARINDEX(',' + CAST(CS.StateID AS VARCHAR) + ',', ',' + @StateIDs + ',') &gt; 0 
    JOIN CourseToCreditType As CC ON CC.CourseID = C.CourseID AND CHARINDEX(',' + CAST(CC.CreditTypeID AS VARCHAR) + ',', ',' + @CreditTypeIDs + ',') &gt; 0
    JOIN CourseToSubject As CSu ON CSu.CourseID = C.CourseID AND CHARINDEX(',' + CAST(CSu.SubjectID AS VARCHAR) + ',', ',' + @SubjectTypeIDs + ',') &gt; 0
  </code></pre>