if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[My_AutoUpdateIfUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[My_AutoUpdateIfUpdate]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCreate proc [dbo].[My_AutoUpdateIfUpdate]
as
declare @Name varchar(25)
BEGIN TRAN
declare ifupdate_cursor CURSOR FOR
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
open ifupdate_cursor
fetch next from ifupdate_cursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
-----------------------------------------------------循环体-----------------------------------------------------------------------------------------
IF Exists(SELECT Name FROM SysColumns WHERE id=Object_Id('@Name') and name = 'ifupdate')
BEGIN
Update @Name set ifupdate='',updateman=''
END
fetch next from ifupdate_cursor INTO @Name
-----------------------------------------------------循环体-----------------------------------------------------------------------------------------
END
CLOSE ifupdate_cursor
DEALLOCATE ifupdate_cursor
if @@error <> 0
begin
rollback tran
return
end
commit tran
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Update @Name set ifupdate='',updateman=''出现错误:
消息 137,级别 15,状态 2,过程 My_AutoUpdateIfUpdate,第 17 行
必须声明变量 '@Name'。
update后不能接变量吗?
请问这个要怎么改?
exec (' Update '+ @Name + 'set ifupdate='',updateman=''' )
BEGIN
exec ('Update ' + @Name + ' set ifupdate='''', updateman=''''') --#2.这里需要用动态sql
END
fetch next from ifupdate_cursor INTO @Name