title: Operand Type Clash
body: <p>I have a long stored procedure and when I execute the procedure I get the following error:</p>
  
  <pre><code>Msg 206, Level 16, State 2, Line 1
  Operand type clash: varchar(max) is incompatible with sql_variant
  </code></pre>
  
  <p>So to trouble shoot I have printed satetement where the problem is and the code is:</p>
  
  <pre><code>SELECT  'Name' ,
                  7 ,
                  CASE WHEN 'varchar' = 'varbinary'
                       THEN REPLACE(UPPER(sys.fn_sqlvarbasetostr([Name])), 'X', 'x')
                       ELSE CONVERT(VARCHAR(4000), [Name])
                  END , 'varchar'
          FROM    ref.dbo.datatables
          WHERE   id = 12
          ORDER BY [ID]
  </code></pre>
  
  <p>So When I execute the above statement it is givng me the error as:</p>
  
  <pre><code>Msg 206, Level 16, State 2, Line 1
  Operand type clash: varchar(max) is incompatible with sql_variant
  </code></pre>
  
  <p>The datatype of Name is Varchar(MAX) in ref.dbo.datatables table</p>
  
  <p>How to solve this issue?</p>
  
  <p>Answer:</p>
  
  <p>This is what I did to work:</p>
  
  <pre><code>SELECT 'Name',
          7,
          CASE WHEN 'varchar' = 'varbinary'
          THEN REPLACE(UPPER(sys.fn_sqlvarbasetostr(CONVERT(VARBINARY,[Name]))),'X','x')
          ELSE CONVERT(VARCHAR(4000),[Name])
          END,
          'varchar'
  FROM ref.dbo.datatables
  WHERE id = 12
  ORDER BY [ID]
  </code></pre>