automatic casting or somethig.. update in dynamic sql

Question

I have dynamic sql in stored procedure:

DECLARE @sql nvarchar(1000)
            SET @sql =  'UPDATE dbo.T_CUS_TSK_TASK '+
                        'SET ' + QUOTENAME(@task_field_name) + '=@value ' +
                        'WHERE company_id=@company_id AND task_id=@task_id'
                        print(@sql)
            EXEC sp_executesql
                @sql,
                N'@company_id uniqueidentifier, @task_id bigint, @value nvarchar(50)',
                @company_id, @task_id, @value

the problem is that I don't know if the field represented by @task_field_name is bigint or nvarchar so I get converting error:

Error converting data type nvarchar to bigint.

How can I prevent the error?

Answer 1

There are only 2 cases I can think of that will give you that error message.

1. The field task_id in the database is not actually bigint. If it is varchar and contains something like '' (blank string), then the WHERE clause can fail because the column is being compared to a bigint variable @task_id.

2. @task_field_name is set to a bigint column, and the value in @value is not convertible to a bigint. This is surely a programming error, because the TSQL code actually works fine as long as it is given proper input - this is what Martin is trying to show you.

Re (2), let's say @value contains the string 'A'. And you have asked to update a bigint column with that value - surely it should fail!? If @value contained any valid bigint value (even within a nvarchar(50) variable) the code does work.

Answer 2

Don't use dynamic SQL and don't try to write generic UPDATE code. It doesn't save you any time, as you've found out and now you've had to ask here.

Either combine into one...

UPDATE
   dbo.T_CUS_TSK_TASK
SET
   bigintColumn = CASE WHEN @task_field_name = 'bigintColumn'
                                   THEN CAST(@value AS bigint) ELSE bigintColumn END
   nvarcharColumn = CASE WHEN @task_field_name = 'nvarcharColumn'
                                   THEN @value ELSE nvarcharColumn END
WHERE
   company_id = @company_id AND task_id = @task_id

...or separate

IF @task_field_name = 'bigintColumn'
    UPDATE
       dbo.T_CUS_TSK_TASK
    SET
       bigintColumn = CAST(@value AS bigint)
    WHERE
       company_id = @company_id AND task_id = @task_id
ELSE
    UPDATE
       dbo.T_CUS_TSK_TASK
    SET
       nvarcharColumn = @value
    WHERE
       company_id = @company_id AND task_id = @task_id

Personally, I wouldn't have generic @value as a parameter either. I'd have separate code or pass in two parameters, one per column.