Parameterizing Dynamic SQL issue with SQL_VARIANT datatype.

Question

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.

I have an int variable (@i) that determines what column I need to update. Here's what I've been doing:

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 ...

Then, I try to execute my statement:

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;

This results in:

Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.

I can get around this if I can somehow cast @updateValue into the correct type, which I have stored as text in @updateClauseDataType. Is there any way I can do this without having a huge if statement that interrogates the value of @updateClauseDataType?

Answer 1

On rereading, since you already know the data type, why not include it in the update clause?

set @updateClause = 'Column1 = cast(@newValue as varchar(10))';
set @updateClauseDataType = 'varchar(10)';

Or to avoid typing the data type twice:

set @updateClauseDataType = 'varchar(10)';
set @updateClause = 'Column1 = cast(@newValue as ' +
    @updateClauseDataType + ')';