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,
开发者_如何学Python 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?
There are only 2 cases I can think of that will give you that error message.
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.
@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.
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.
精彩评论