(4.61)sql server执行SQL遇到错误不中断
转自:CSDN 问题贴:https://bbs.csdn.net/topics/399171379
【1】问题
execute sp_addextendedproperty 'MS_Description','XXXX','user','dbo','table','TB1',null,null; execute sp_addextendedproperty 'MS_Description','XXXX2','user','dbo','table','TB2',null,null; 在SSMS中执行的时候如果第1个报错了,第2个不会执行被中断了 只有这种才可以 execute sp_addextendedproperty 'MS_Description','XXXX','user','dbo','table','TB1',null,null; GO execute sp_addextendedproperty 'MS_Description','XXXX2','user','dbo','table','TB2',null,null; 但是我的语句都是SQL拼接出来的,没法出现换行的GO
【2】解决,使用 begin try cache(或者使用存储过程)
--1. BEGIN TRY execute sp_addextendedproperty 'MS_Description','XXXX','user','dbo','table','TB1',null,null; END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage END CATCH --2. BEGIN TRY execute sp_addextendedproperty 'MS_Description','XXXX2','user','dbo','table','TB2',null,null; END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage END CATCH
【3】参考写法(+char(10)+'go'+char(10))
DECLARE @sql VARCHAR(max)='' SELECT @sql=@sql+'execute sp_addextendedproperty ''MS_Description'',''XXXX'',''user'',''dbo'',''table'','''+name+''',null,null;'+CHAR(10)+'go'+CHAR(10) FROM sys.objects WHERE type='U' PRINT @sql
这样的结果,你可以复制出来用
但不能直接 exec(@sql)
否则会报错: