title: Parameterizing Dynamic SQL issue with SQL_VARIANT datatype
body: <p>I've been refactoring a huge dynamic SQL statement, and think I may have hit a roadblock. The goal has been to parameterize it. I'm using SQL Server 2005.</p>
  
  <p>I have an int variable (<code>@i</code>) that determines what column I need to update. Here's what I've been doing:</p>
  
  <pre><code>if @i = 1
   begin
    set @updateClause = 'Column1 = @newValue';
    set @updateClauseDataType = 'varchar(10)';
    set @updateValue = @foo;      
   end
  else if @i = 2
   begin
    set @updateClause = 'Column2 = @newValue';
    set @updateClauseDataType = 'int';
    set @updateValue = @bar;      
   end
  else if ...
  </code></pre>
  
  <p>Then, I try to execute my statement:</p>
  
  <pre><code>set @statement = N'update ' + @server_name + '.' + @database_name + '.dbo.Table1 
    set ' + @updateClause + ' where pk = @pk';
  
  set @parameters = '@newValue ' + @updateClauseDataType + ',      
   @pk uniqueidentifier';
  
  
  execute sp_executesql @statement, 
   @parameters,
   @newValue = @newValue,
   @pk= @pk;
  </code></pre>
  
  <p>This results in:</p>
  
  <blockquote>
    <p>Implicit conversion from data type
    sql_variant to varchar is not allowed.
    Use the CONVERT function to run this
    query.</p>
  </blockquote>
  
  <p>I can get around this if I can somehow cast <code>@updateValue</code> into the correct type, which I have stored as text in <code>@updateClauseDataType</code>. Is there any way I can do this without having a huge if statement that interrogates the value of <code>@updateClauseDataType</code>?</p>