sql-server-2008 - 消息102，级别15，状态1，行1'<'附近的语法不正确

Question

我正在尝试为MSSQL 2008 R2数据库编写存储过程。
存储过程应比较一些值并返回一个位/布尔值。
根据参数（@PlatformCode），该过程应选择正确的检查。

但是我在尝试将结果存储到输出var的行中遇到错误。

CREATE PROCEDURE CheckVersion 
    @PlatformCode nvarchar(4),
    @ClientVersion int,
    @ConfigId int = 1, 
    @Response bit OUTPUT 

AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @version int
    IF @PlatformCode = 'a' 
    BEGIN
        SET @version = (SELECT aVersion FROM AppConfigTable WHERE ConfigID = @ConfigId)
        SET @Response = (@version <= @ClientVersion)
    END 
    IF @PlatformCode = 'i'
    BEGIN
        SET @version = (SELECT iVersion FROM AppConfigTable WHERE ConfigID = @ConfigId)
        SET @Response = (@version <= @ClientVersion)
    END
END
GO

是否有更好的方法返回结果？

Answer 1

您缺少的是CASE语句

CREATE PROCEDURE CheckVersion 
    @PlatformCode nvarchar(4),
    @ClientVersion int,
    @ConfigId int = 1, 
    @Response bit OUTPUT 

AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @version int
    IF @PlatformCode = 'a' 
    BEGIN
        SET @version = (SELECT aVersion FROM AppConfigTable WHERE ConfigID = @ConfigId)
        SET @Response = CASE WHEN (@version <= @ClientVersion) THEN 1 ELSE 0 END
    END 
    IF @PlatformCode = 'i'
    BEGIN
        SET @version = (SELECT iVersion FROM AppConfigTable WHERE ConfigID = @ConfigId)
        SET @Response = CASE WHEN (@version <= @ClientVersion) THEN 1 ELSE 0 END
    END
END
GO

更好的是，您可以这样做：

CREATE PROCEDURE CheckVersion 
    @PlatformCode nvarchar(4),
    @ClientVersion int,
    @ConfigId int = 1, 
    @Response bit OUTPUT 

AS
BEGIN
    SET NOCOUNT ON; 

    IF @PlatformCode = 'a' 
    BEGIN
       SELECT @Response = CASE WHEN aVersion <= @ClientVersion  THEN 1 ELSE 0 END FROM AppConfigTable WHERE ConfigID = @ConfigId         
    END 
    IF @PlatformCode = 'i'
    BEGIN
       SELECT @Response = CASE WHEN iVersion <= @ClientVersion  THEN 1 ELSE 0 END FROM AppConfigTable WHERE ConfigID = @ConfigId         
    END
END
GO