动态光标动态更新 [英] Dynamic Update with Dynamic Cursor

问题描述

程序基本要点

1-i有一个表类型为int的主键

2-i在此表类型uniqueidentifier中有acolumn，其名称相同主键的名称+单词备用

3-i使用此备用列根据主键值放置uniqueidentifier

存储过程是

procedure basic Points
1-i have a table with primary key of type int
2-i have acolumn in this table of type uniqueidentifier its name is the same name of primarykey + the word spare
3-i use this spare column to put a uniqueidentifier depending on primary key value
the stored procedure is

Create Procedure Update_Values
@tablename as nvarchar(255),
@PrimaryKey as nvarchar(255),
@IDValue as uniqueidentifier
as
Declare @Declarestatement as nvarchar(1000)
Declare @SelectStatement as nvarchar(1000)
Declare @UpdateStatement as nvarchar(1000)
Declare @ID as int 
Declare @A as  Cursor
Set  @SelectStatement = 'Select ' + @PrimaryKey +  ' From ' + @tablename
Set  @Declarestatement = 'Set @cursor = cursor forward_only static for ' + @SelectStatement + ' open @cursor;'
Exec(@DeclareStatement)
Exec (@DeclareStatement)
while (@@fetch_status = 0)
begin
Fetch Next From A Into @ID
Set @UpdateStatement = 'Update ' + @tablename + ' Set '  + @PrimaryKey + '_Spare' + '=' + @IDvalue+  'Where ' + @PrimaryKey + '=' + @ID
Exec(@UpdateStatement)
end
Close A 
Deallocate A

但我收到以下错误

but i got the following error

Msg 402, Level 16, State 1, Procedure Update_Values, Line 18
The data types nvarchar and uniqueidentifier are incompatible in the add operator.

我收到错误的行是

the line which i get the error in is

Set @UpdateStatement = 'Update ' + @tablename + ' Set '  + @PrimaryKey + '_Spare' + '=' + @IDvalue+  'Where ' + @PrimaryKey + '=' + @ID89

问题是@primarykey是关键名称

你可以帮我吗

the problem is @primarykey is aprimary key name
can you help me

推荐答案

尝试下面

try below

Set @UpdateStatement = 'Update ' + @tablename + ' Set '  + @PrimaryKey + '_Spare' + '=' + @IDvalue+  ' Where ' + @PrimaryKey + '=' + convert(nvarchar(36), @ID89)

或

or

Set @UpdateStatement = 'Update ' + @tablename + ' Set '  + @PrimaryKey + '_Spare' + '=' + @IDvalue+  ' Where ' + @PrimaryKey + '=' + CAST(@ID89 as nvarchar )

这篇关于动态光标动态更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！