title: SQL Applying a parameter instead of hard coding
body: <p>I need a little help, I have a report that I am running that only pulls out certain information based on a number of 'categories' that are hard coded. However, I want to make this as flexible as possible and I want to replace these coded categories with a parameter that can be selected by the user. However when I attempt this it never works for various reasons. The current code is:</p>
  
  <pre><code>    SELECT          gcs_allocatedpdaidname AS PDA,
          gcs_consideringapplyingyearname AS 'Intending to Start ITT', 
          SUM(CASE WHEN (gcs_allocatedpdaid IS NOT NULL) AND (gcs_ContactType = 1) THEN 1 ELSE 0 END) AS 'Total Participants',
          SUM(CASE WHEN (gcs_allocatedpdaid IS NOT NULL) AND (gcs_ContactType = 1) AND (StatusCode = 1) THEN 1 ELSE 0 END) AS 'Active Participants', 
          SUM(CASE WHEN (gcs_allocatedpdaid IS NOT NULL) AND (gcs_ContactType = 1) AND (StatusCode = 200001) THEN 1 ELSE 0 END) AS 'Completed Participants', 
          SUM(CASE WHEN (gcs_allocatedpdaid IS NOT NULL) AND (gcs_ContactType = 1) AND ((gcs_categoryofparticipant = 7) OR
                              (gcs_categoryofparticipant = 8) OR
                              (gcs_categoryofparticipant = 9) OR
                              (gcs_categoryofparticipant = 10) OR
                              (gcs_categoryofparticipant = 11) OR
                              (gcs_categoryofparticipant = 12)) THEN 1 ELSE 0 END) AS 'On ITT and beyond TOTAL',
  
          SUM(CASE WHEN (gcs_allocatedpdaid IS NOT NULL) AND (gcs_ContactType = 1) AND ((StatusCode = 1) OR (StatusCode = 200001)) THEN 1 ELSE 0 END) AS 'On ITT and beyond ACTIVE/COMPLETE'
  
  FROM            FilteredContact
  GROUP BY gcs_allocatedpdaidname, gcs_allocatedpdaid, gcs_consideringapplyingyearname
  HAVING        (gcs_allocatedpdaidname IN (@PDA)) AND (gcs_consideringapplyingyearname IN (@ITTYear))
  ORDER BY PDA, 'Intending to Start ITT'
  </code></pre>
  
  <p>The hard coding I want to replace is the 'gcs_categoryofparticipant', see below:</p>
  
  <pre><code>SUM(CASE WHEN (gcs_allocatedpdaid IS NOT NULL) AND (gcs_ContactType = 1) AND
                                  ((gcs_categoryofparticipant = 7) OR
                                  (gcs_categoryofparticipant = 8) OR
                                  (gcs_categoryofparticipant = 9) OR
                                  (gcs_categoryofparticipant = 10) OR
                                  (gcs_categoryofparticipant = 11) OR
                                  (gcs_categoryofparticipant = 12)) THEN 1 ELSE 0 END) AS 'On ITT and beyond TOTAL'
  </code></pre>
  
  <p>I imagine it would be something along the lines of:</p>
  
  <pre><code>SUM(CASE WHEN (gcs_allocatedpdaid IS NOT NULL) AND (gcs_ContactType = 1) AND (HAVING (gcs_categoryofparticipant IN (@Category))) THEN 1 ELSE 0 END) AS 'On ITT and beyond TOTAL'
  </code></pre>
  
  <p>This obviously doesnt work but if someone could point me in the right direction it would be greatly appreciated.</p>
  
  <p>Thanks</p>