CODEWITHSUNDEEP
c#sqldatabasestored-procedures
erhanzeyrek
erhanzeyrek

Reputation: 36

Stored Procedure return values by comparison of 2 fields

I have a table that contains 3 columns USERID, OLDPASSWORD, NEWPASSWORD.

I want to select OLDPASSWORD and NEWPASSWORD columns by USERID and compare OLDPASSWORD and NEWPASSWORD, if there is an equality between NEWPASSWORD and OLDPASSWORD, It should return NEWPASSWORD, otherwise it returns 0 (string);

Someting like this:

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

Thanks for your help.

Upvotes: 0

Views: 378

Answers (6)

Bhavin
Bhavin

Reputation: 260

// 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

Upvotes: 0

Kumod Singh
Kumod Singh

Reputation: 2163

It is not possible, to return more than one value using return values, where as output parameters, can return any datatype and an sp can have more than one output parameters. I always prefer, using output parameters, over RETURN values.

In general, RETURN values are used to indicate success or failure of stored procedure, especially when we are dealing with nested stored procedures.Return a value of 0, indicates success, and any nonzero value indicates failure.

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

Upvotes: 0

Shiju Shaji
Shiju Shaji

Reputation: 1730

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

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Return keyword is not designed for returning values from table. Return keyword is for returning from stored procedure and it can return only integer values. Here you can return some code result of executing your proc. You should directly do this with select statement or by having some 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

Or:

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

Upvotes: 1

Thanos Markou
Thanos Markou

Reputation: 2624

You need something like that:

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

Upvotes: 0

msmolcic
msmolcic

Reputation: 6557

Have you tried with this:

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

Upvotes: 0

Related Questions