存储过程通过比较两个字段返回值

本文关键字：两个 字段 返回值 比较 存储过程 | 更新日期: 2023-09-27 18:02:04

我有一个包含3列USERID, OLDPASSWORD, NEWPASSWORD的表。

我想通过USERID选择OLDPASSWORD和NEWPASSWORD列，比较OLDPASSWORD和NEWPASSWORD，如果NEWPASSWORD和OLDPASSWORD相等，它应该返回NEWPASSWORD，否则返回0(字符串);

像这样:

ALTER PROCEDURE [dbo].[GET_PASSWORD]
(
    @USERID NVARCHAR(50)
)
AS
     SELECT OLDPASSWORD, NEWPASSWORD 
     FROM PASSWORDINFO 
     WHERE USERID = @USERID
     IF OLDPASSWORD == NEWPASSWORD 
        RETURN "0" 
     ELSE 
        RETURN NEWPASSWORD

谢谢你的帮助。

存储过程通过比较两个字段返回值

Return关键字不是为从表返回值而设计的。Return关键字用于从存储过程返回，它只能返回整数值。这里你可以返回一些执行过程的代码结果，你应该直接使用select语句或通过output parameter语句:

ALTER PROCEDURE [dbo].[GET_PASSWORD] @USERID NVARCHAR(50)
AS
    BEGIN
        SELECT  CASE WHEN OLDPASSWORD = NEWPASSWORD THEN '0'
                     ELSE NEWPASSWORD
                END AS CurrentPassword
        FROM    PASSWORDINFO
        WHERE   USERID = @USERID
    END

或:

ALTER PROCEDURE [dbo].[GET_PASSWORD]
    @USERID NVARCHAR(50) ,
    @CurrentPassword NVARCHAR(50) OUTPUT
AS
    BEGIN
        SELECT  @CurrentPassword = CASE WHEN OLDPASSWORD = NEWPASSWORD
                                        THEN '0'
                                        ELSE NEWPASSWORD
                                   END
        FROM    PASSWORDINFO
        WHERE   USERID = @USERID
    END

你试过了吗:

ALTER PROCEDURE [dbo].[GET_PASSWORD]
(
    @USERID NVARCHAR(50)
)
AS
BEGIN
    SELECT
        CASE
            WHEN OLDPASSWORD = NEWPASSWORD THEN '0'
            ELSE NEWPASSWORD
         END
    FROM PASSWORDINFO
    WHERE USERID = @USERID
END

你需要这样的东西:

   SELECT CASE WHEN OLDPASSWORD = NEWPASSWORD THEN '0' 
   ELSE NEWPASSWORD END AS Result 
   FROM PASSWORDINFO WHERE USERID = @USERID

ALTER PROCEDURE [dbo].[GET_PASSWORD] (@USERID NVARCHAR(50), @result VARCHAR(10) OUTPUT)
AS
BEGIN
    SELECT @result = CASE 
            WHEN OLDPASSWORD = NEWPASSWORD
                THEN '1'
            ELSE '0'
            END
    FROM PASSWORDINFO
    WHERE UserId = @USERID
END

使用返回值返回多个值是不可能的，其中作为输出参数，可以返回任何数据类型，并且sp可以有多个输出参数。我总是更喜欢使用输出参数，而不是返回值。

一般来说，RETURN值用于指示存储过程的成功或失败，特别是在处理嵌套存储过程时。返回值为0，表示成功，任何非0的值都表示失败。

ALTER PROCEDURE [dbo].[GET_PASSWORD]
    @USERID NVARCHAR(50) ,
    @Password NVARCHAR(50) OUTPUT
AS
    BEGIN
        SELECT  @Password  = CASE WHEN OLDPASSWORD = NEWPASSWORD
                                THEN '0'
                                  ELSE NEWPASSWORD
                                END
        FROM    PASSWORDINFO
        WHERE   USERID = @USERID
    END

// try this
ALTER PROCEDURE [dbo].[GET_PASSWORD]
(
    @USERID NVARCHAR(50)
)
AS
    declare   @NewPassword NVARCHAR(50)
     declare   @OldPassword NVARCHAR(50)
     SELECT  @OldPassword =OLDPASSWORD, @NewPassword = NEWPASSWORD 
     FROM PASSWORDINFO 
     WHERE USERID = @USERID    
     IF @OldPassword = @NewPassword 
        RETURN "0" 
     ELSE 
        RETURN @NewPassword
相关文章：