时间戳

首页 > 解决方案 > 如果结果存在则执行存储过程

sql-server - 如果结果存在则执行存储过程

问题描述

我想对现有的存储过程有一些帮助，我想从我有匹配记录的表[DVMT30_EVENT_PROCESSOR].[StateMachine].[Snapshots]中获得输出，我们有匹配的记录CrossbarRouter = @CrossbarRouter是存储过程的一部分，

我只想在[StateMachine].[UpdateSnapshots]结果@OUT集与@Crossbar参数匹配时执行存储过程。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @OUT NVARCHAR(100); 

CREATE PROCEDURE [StateMachine].[UpdateSnapshots] 
    @SystemName [NVARCHAR](128), 
    @IntrestingEvents VARCHAR(128), 
    @StateMachine_JSON [NVARCHAR](MAX),
    @StateMachine_Object [NVARCHAR](MAX),
    @CrossbarRouter VARCHAR(128)
AS
    SET @OUT = (SELECT [CrossbarRouter] 
                FROM [DVMT30_EVENT_PROCESSOR].[StateMachine].[Snapshots] 
                WHERE CrossbarRouter = @CrossbarRouter)

    IF EXISTS (SELECT * FROM [DVMT30_EVENT_PROCESSOR].[StateMachine].[Snapshots] 
               WHERE SystemName = @SystemName)
        UPDATE [DVMT30_EVENT_PROCESSOR].[StateMachine].[Snapshots]
        SET IntrestingEvents = @IntrestingEvents, 
            StateMachine_JSON = @StateMachine_JSON, 
            StateMachine_Object = @StateMachine_Object, 
            CrossbarRouter = @CrossbarRouter
        WHERE SystemName = @SystemName;
    ELSE
        INSERT INTO [DVMT30_EVENT_PROCESSOR].[StateMachine].[Snapshots] (SystemName, IntrestingEvents, StateMachine_JSON, StateMachine_Object, CrossbarRouter)
        VALUES (@SystemName, @IntrestingEvents, @StateMachine_JSON, @StateMachine_Object, @CrossbarRouter);
GO

标签： sql-serverstored-procedures

解决方案

如果没有匹配，您可以返回：

SELECT @OUT = [CrossbarRouter] 
    FROM [DVMT30_EVENT_PROCESSOR].[StateMachine].[Snapshots] 
    WHERE CrossbarRouter = @CrossbarRouter

IF @OUT IS NULL 
    RETURN 0;

推荐阅读