title: Arithmetic/Logical operation's based on Column value
body: <p>I want to do arithmetic operation based on my column values. Consider the following example </p>
  
  <pre><code>CREATE TABLE #test
    (
       cont_sal    INT,
       check_value INT,
       operator    VARCHAR(50)
    )
  
  INSERT #test
  VALUES (10,20,'+'),
         (20,10,'+'),
         (10,20,'-'),
         (20,10,'-') 
  </code></pre>
  
  <p>Expected Result: </p>
  
  <pre><code>cont_sal    check_value result
  --------    ----------- ------
  10          20          30
  20          10          30
  10          20          -10
  20          10          10
  </code></pre>
  
  <p>I can do this using <code>CASE</code> statement. </p>
  
  <pre><code>SELECT cont_sal,
         check_value,
         CASE
           WHEN operator = '+' THEN cont_sal + check_value
           when operator = '-' THEN cont_sal - check_value
         END result
  FROM   #test 
  </code></pre>
  
  <p>But is there any way to do this dynamically. Operator's can be anything like <code>/</code>,<code>%</code>,<code>*</code>. Something like this </p>
  
  <pre><code>DECLARE @sql NVARCHAR(max)=''
  
  SET @sql = 'select cont_sal ' + 'operator'
             + ' check_value from #test '
  
  --PRINT @sql
  
  EXEC Sp_executesql
    @sql 
  </code></pre>
  
  <p>Which obviously didn't work saying </p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Line 1 Incorrect syntax near
    'check_value'.</p>
  </blockquote>