tsql - 在存储过程中更新之前验证记录

Question

假设我有 2 个县:

• 一个
• B

每个都有自己的 URL:

• www.A.com
• www.B.com

我有一个存储过程，它接受 3 个变量来启用或禁用对 URL 的服务。 @Enable 变量是必需的，但用户可以输入 @County_Code 或 @WebserviceURL 变量 - 或两者。但如果他们同时输入，我想验证该记录是否存在于数据库中。

例如，

EXEC [dbo].usp_webservice_change_status
@启用= 1，
@county_code = 'A',
@webserviceURL = 'www.A.com';

应该执行并更新启用标志。

但如果我使用以下值执行，我希望返回一个错误，说明 A 县没有对应的 www.B.com 值。

EXEC [dbo].usp_webservice_change_status
@启用= 1，
@county_code = 'A',
@webserviceURL = 'www.B.com';

完整的存储过程如下:

ALTER PROCEDURE [dbo].[usp_webservice_change_status]
@enable AS BIT,
@county_code AS CHAR(2) = NULL,
@webserviceURL AS VARCHAR(4000) = NULL

AS
    BEGIN
        SET NOCOUNT ON;
        IF @enable IS NULL
            RAISERROR ('The value for @enable should not be null', 15, 1);
        IF ( @county_code IS NULL AND @webserviceURL IS NULL )
            RAISERROR ('The value for @county_code and @webserviceURL cannot both be null', 15, 1);

IF ( @county_code IS NOT NULL AND @webserviceURL IS NULL )

UPDATE dbo.webservice_config
SET [enable] = @enable,
    comments = CASE
                   WHEN @enable = 1 THEN 'Enabled by ' + SUSER_SNAME()
                   ELSE 'Disabled by ' + SUSER_SNAME()
               END    
WHERE county_code = @county_code

ELSE IF (@county_code IS NULL AND @webserviceURL IS NOT NULL )

UPDATE dbo.webservice_config
SET [enable] = @enable,
    comments = CASE
                   WHEN @enable = 1 THEN 'Enabled by ' + SUSER_SNAME()
                   ELSE 'Disabled by ' + SUSER_SNAME()
               END    
WHERE webservice_URL = @webserviceURL

ELSE IF ( @county_code IS NOT NULL AND @webserviceURL IS NOT NULL )

UPDATE dbo.webservice_config
SET [enable] = @enable,
    comments = CASE
                   WHEN @enable = 1 THEN 'Enabled by ' + SUSER_SNAME()
                   ELSE 'Disabled by ' + SUSER_SNAME()
               END    
WHERE ( county_code = @county_code AND webservice_URL = @webserviceURL )

    END;

Answer 1

如果我正确理解您想要什么，您可以使用另一个 IF 语句来执行此操作...只需在 PROCEDURE 底部的 END; 之前添加这两行>

IF(@@ROWCOUNT) < 1
RAISERROR('Nothing Updated Due to Non Matching Records',15,1)

所以它看起来像这样

--all your other code
WHERE ( county_code = @county_code AND webservice_URL = @webserviceURL )
IF(@@ROWCOUNT) < 1
RAISERROR('Nothing Updated Due to Non Matching Records',15,1)
END;

如果需要，您也可以将整个内容包装在另一个 IF 中

ELSE IF ( @county_code IS NOT NULL AND @webserviceURL IS NOT NULL )

    IF(SELECT 1 FROM dbo.webservice_config  WHERE county_code = @county_code AND webservice_URL = @webserviceURL) < 1
        RAISERROR('Nothing Updated Due to Non Matching Records',15,1)
    ELSE
        UPDATE dbo.webservice_config
        SET [enable] = @enable,
            comments = CASE
                           WHEN @enable = 1 THEN 'Enabled by ' + SUSER_SNAME()
                           ELSE 'Disabled by ' + SUSER_SNAME()
                       END    
        WHERE ( county_code = @county_code AND webservice_URL = @webserviceURL )

END;

您也可以使用 EXISTS 和其他语法来代替我使用的...只是一些示例。