如果前一个查询完成且没有错误，如何在SQL server中运行顺序查询

Question

我在SQL Server中创建了几个查询来获取报告，但如果第一个查询运行并完成时没有错误，我希望按顺序自动运行它们。请看一下发布的示例：

-------------------------------------------------------------------------------------------------
WITH cte AS (SELECT[WO], row_number() OVER(PARTITION BY WO order by [WO]) AS [rn],
[WeekDay],[now] FROM [MESF_1].[dbo].[Server])
DELETE cte WHERE [rn] > 1 and (DATEDIFF(DAY,[NOW],GETDATE())) < 20 and [Weekday] is not null
USE [MESF_1]
GO
---------------------------------------------------------------------------------------------------
UPDATE [MESF_1].[dbo].[Server]
SET [Time] = Format ([NOW],'HH:mm:ss')
WHERE [Time] is null
GO
---------------------------------------------------------------------------------------------------
UPDATE [MESF_1].[dbo].[Server]
SET [WeekDay] = case datepart(WEEKDAY,CONVERT(date,[NOW],103)) 
when 1 then'Sunday' When 2 then 'Monday' When 3 then'Tuesday' When 4 then 'Wednesday' 
when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' end
FROM [MESF_1].[dbo].[Server]
where [WeekDay] is null
---------------------------------------------------------------------------------------------------

查询用短划线分隔。提前感谢

Answer 1

我想你正在寻找这样的东西。通过检查@@rocount>0之后，如果第一次更新不影响任何行，则以下2条UPDATE语句永远不会运行。若要在发生异常后继续执行，DML语句将放置在Try/Catch块中。

USE [MESF_1];
set nocount on;
begin try
WITH cte AS (
SELECT [WO], [WeekDay],[now],
row_number() OVER(PARTITION BY WO order by [WO]) AS [rn]
FROM [dbo].[Server])
DELETE cte 
WHERE [rn] > 1 
and (DATEDIFF(DAY,[NOW],GETDATE())) < 20 
and [Weekday] is not null;
if @@rowcount>0
begin
UPDATE [dbo].[Server]
SET [Time] = Format ([NOW],'HH:mm:ss')
WHERE [Time] is null;
UPDATE [dbo].[Server]
SET [WeekDay] = case datepart(WEEKDAY,CONVERT(date,[NOW],103)) 
when 1 then'Sunday' When 2 then 'Monday' When 3 then'Tuesday' 
When 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday'
when 7 then 'Saturday' end
WHERE [WeekDay] is null;
end
end try
begin catch
print ('There was an exception thrown');
/* Logging, raiserror, throw*/
/* rollback (if transaction requires*/
end catch

Answer 2

你是说这个吗

WITH cte AS (SELECT[WO], row_number() OVER(PARTITION BY WO order by [WO]) AS [rn],
[WeekDay],[now] FROM [MESF_1].[dbo].[Server])
DELETE cte WHERE [rn] > 1 and (DATEDIFF(DAY,[NOW],GETDATE())) < 20 and [Weekday] is 
not null
USE [MESF_1]
GO
-------------------------------------------------------------------------------------- 
if @@Error=0 then
begin
UPDATE [MESF_1].[dbo].[Server]
SET [Time] = Format ([NOW],'HH:mm:ss')
WHERE [Time] is null
GO
end
-------------------------------------------------------------------------------------- 
if @@Error=0 then
begin
UPDATE [MESF_1].[dbo].[Server]
SET [WeekDay] = case datepart(WEEKDAY,CONVERT(date,[NOW],103)) 
when 1 then'Sunday' When 2 then 'Monday' When 3 then'Tuesday' When 4 then 'Wednesday' 
when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' end
FROM [MESF_1].[dbo].[Server]
where [WeekDay] is null
end