(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)

否则会报错:

  

 

posted @ 2021-05-08 09:49  郭大侠1  阅读(447)  评论(0编辑  收藏  举报