title: How to pass dynamically created filters as parameter of stored procedure in SQL Server and filter data?
body: <p>How can I pass some <strong>dynamic filters</strong> (populated from my table <code>tblMarketingCampaignFilters</code> in SQL Server) and compare them to a <code>TargetColumn</code> in <code>tblMarketingUsers</code>? </p>
  
  <p>Just to clarify, I have all the code to generate and populate the filters with the <code>InputTypes</code> assigned (it can be numbers, dates, strings, or boolean with checkboxes, only), but the <em>page</em> is supposed to Add as much a user wants (<em>n</em> filters). </p>
  
  <p>Here is an image showing how my tables are defined</p>
  
  <p><a href="https://i.stack.imgur.com/KFdSX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KFdSX.png" alt="tables used"></a></p>
  
  <p>I must compare the <em>filters</em>, with <em>columns</em> (to be added) in <code>tblMarketingUsers</code>.</p>
  
  <p>Examples:</p>
  
  <ul>
  <li><em>LastLoginDate</em> > '20150701' if it is a Date,</li>
  <li><em>Advanced Profile Status</em> > 50(percent) if it is a number,</li>
  <li><em>InBothSystems</em> = 0 if it is a checkbox checked</li>
  </ul>
  
  <p>This is my filter part of the form in the <em>page</em></p>
  
  <p><a href="https://i.stack.imgur.com/mfwcf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mfwcf.png" alt="client-side part"></a></p>
  
  <p>By now, I have only 4 filters, it could be 10, I don't know.</p>
  
  <p>Thanks.</p>
  
  <p>PS: I think that I am missing a <code>TableTarget</code> in <code>tblMarketingCampaignFilters</code>, if the system have to look on another table, not only <code>tblMarketingUsers</code>, but it doesn't matter so much.</p>
  
  <p>PS2: I am considering passing the filters as text, with the comma-separated style, and then insert this into <code>transMarketingCampaignFilters</code>, but still don't know how to <em>compare</em> them dynamically, my boss says <em>"use cursors in SQL"</em></p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Thanks for the replies, and comments.</p>
  
  <p>This is an approach of what I am thinking to do. I will need to add <em>Operator</em> column in <strong>tblMarketingCampaignFilters</strong> (it will store something like : '<strong>=</strong>' , '<strong>&lt;</strong>', '<strong>></strong>', '<strong>LIKE</strong>') , and btw, this stored procedure will assume that a Campaign with the filters has already submitted on the form, and all data is stored on <strong>transMarketingCampaignFilters</strong></p>
  
  <p><pre>
      CREATE PROCEDURE usp_MarketingUsersGetFiltered
      -- Add the parameters for the stored procedure here
      @CampaignId int,
      @Debug BIT = 0
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      declare @sql varchar(max) = '', @Nsql nvarchar(max), @DatabaseId INT;
      -- Insert statements for procedure here</p>
  
  <code>set @DatabaseId = (SELECT DatabaseId from tblMarketingCampaigns where Id=@CampaignId);
  select @sql +=  ' AND '+quotename(f.TargetColumn) + f.Operator + 
              case f.InputType 
              when 'int' then cast(cf.Value as int)
              else quotename(cf.Value,'''')               
              end
  from dbo.transMarketingCampaignFilters as cf
  join dbo.tblMarketingCampaignFilters as f
  on cf.CampaignFilterId = f.Id
  where cf.CampaignId = @CampaignId
  
   set @sql = '
   select MarketingUserId,
          FirstName,
          LastName,
          Email,
          CompanyId
   from dbo.tblMarketingUsers 
   where DatabaseId = @DatabaseId
   '+ @sql+'
   OPTION (RECOMPILE)';
  
  if @Debug = 1 print @sql;
  set @sql = @Nsql;
  exec sys.sp_executesql @Nsql,N'@DatabaseId INT',@DatabaseId;
  
  END
  GO
  </code></pre>
  
  <p></p>