两个update against one table in MSSQL 2008R2
0
悬赏园豆：5
[已解决问题]
解决于 2021-02-18 15:46
请教：
我有两个update 语句：
update tb_menu3
set selected = 0
where selected = 1
update tb_menu3
set selected = 1
where mid in ('m022','m023','m032'))
select * from tb_menu3
where selected = 1;
放在MSSMS中执行，没有问题。三条数据被更新；但我将上述语句放在stored procedure 中执行，没有出现被更新的三条数据（即使我将上述update 分别放在Begin Try
Begin TransAction end TransAction end try 中 ），为啥？
谢谢！
最佳答案
0
proc 的代码贴出来
收获园豆：5
Thanks!
我有多个版本， 都有问题：
version 2 so far.
@Name varchar(100)
declare @statement1 varchar(100);
set @statement1 =
'update tb_menu3 set selected = 1 where mid in ('+ @Name + ')';
print @statement1
exec @statement1;
update tb_menu3 set selected = 1 where mid in ('m013','m042','m104')
Msg 2812, Level 16, State 62, Procedure usp_newSelectedQuickMenu, Line 27
Could not find stored procedure 'update tb_menu3 set selected = 1 where mid in ('m013','m042','m104')'.
version2 so far, I had something at least, but still issue remains.
OK. above was my grammar.
Now, I made changes on:
to exec (@statement1); from exec @statement1;
It seems update works.
version１. did not work
create procedure xxx as
(
@Name varchar(100)
)
begin
Begin Try
Begin TransAction
update tb_menu3
set selected = 0
where selected = 1
update tb_menu3
set selected = 1
where mid in (@Name)
Commit TransAction
End Try
Begin Catch
Rollback TransAction
End Catch
end
It looks worked. Please checked below details:
create procedure xyz
(
@Name varchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
update tb_menu3 set selected = 0;
declare @statement1 varchar(100);
set @statement1 =
'update tb_menu3 set selected = 1 where mid in ('+ @Name + ')';
--print @statement1
exec( @statement1);
It looks like the variable @NAME had issues.
@NAME is from an aspx page, and I was using Jquery to build a string and assigned to @NAME.
It might be the issue of HTML code or plan text code. I got confused on this.
But, so far, this is a workable version.
Many thanks for your help.
