title: Creating a dynamic where clause in SQL Server stored procedure
body: <p>I am trying to create a stored that will accept two values, a column name and a value. It will then check if there is a record that exists for the passed in column name with the passed in value. I've tried the following</p>
  
  <pre><code>CREATE PROCEDURE p_HasActiveReference
       @pi_colName varchar(100)
      ,@pi_colValue varchar(100)
  AS
  BEGIN
  SET NOCOUNT ON      
      declare @sql varchar(1000)
  
      set @sql = 'IF EXISTS(SELECT TOP 1 p.PaymentId
                  FROM Payments p
                  WHERE ' + @pi_colName + ' = ' + @pi_colValue +     'AND Active = 1)
  
                      SELECT ''True'' AS RETVAL
                  ELSE
                      SELECT ''False'' AS RETVAL'
      exec(@sql)  
   END
  </code></pre>
  
  <p>However, I always get this error</p>
  
  <blockquote>
    <p><em>Conversion failed when converting the varchar value 'InternalOr' to data type int.</em></p>
  </blockquote>
  
  <p>When I call the procedure with the following</p>
  
  <pre><code>p_HasActiveReference 'InternalOrgCode', '10110'
  </code></pre>
  
  <p>The <code>internalOrgCode</code> column is of value <code>varchar(10)</code></p>
  
  <p>I am not a SQL expert, so I am not even sure if what I need to achieve is even possible using that technique!</p>
  
  <p>Thanks!</p>