Problem in stored procedure result

Question

I have a store procedure like this :

ALTER PROCEDURE [dbo].[CheckUser&Pass]
(
    @Username nchar(15),
    @Password nchar(15)
)
AS
    SET NOCOUNT ON;
SELECT        Username, Pass, Code_Person
FROM            Login
WHERE        (Username = @Username) AND (Pass = @Password)

And Execution :

DECLARE @return_value int

EXEC    @return_value = [dbo].[CheckUser&Pass]
        @Username = N'admin',
        @Password = N'1234'

SELECT  'Return Value' = @return_value

GO

I Want to know how I can set @return_Value to 1 when the username is admin and password is 1234.

but it doesn't work properly, is that possible ?

Answer 1

You could change your stored proc to return the count, assuming that username and password are unique you should get a 1 or a 0 for the result, but I reckon there must be a better way.

ALTER PROCEDURE [dbo].[CheckUser&Pass]
(
    @Username nchar(15),
    @Password nchar(15)
)
AS
    SET NOCOUNT ON;
SELECT        Count(Username)
FROM            Login
WHERE        (Username = @Username) AND (Pass = @Password)

Answer 2

To set the return value within a stored procedure, you need to use the RETURN statement

e.g.

IF (@@ROWCOUNT > 0 AND @Username = 'admin' and password='1234')
    RETURN 1
ELSE
    RETURN 0

Please note, I've given exactly what you've asked for just to demonstrate use of the RETURN statement. There's a few concerns I have that should be pointed out:

1. Looks like you're storing the password in plain text, which if is the case, is a bad idea
2. This hardcoded condition for admin username and password combination is horrible. One better solution is to have an "IsAdmin" flag in the db - if the supplied username/password is valid, then return 1 if the flag indicates the account is an admin.
3. Use of nchar - all usernames and passwords will be padded out to 15 characters. You should consider nvarchar instead.

Answer 3

you should change your stored procedure in order to return the value you want

ALTER PROCEDURE [dbo].[CheckUser&Pass]
(
    @Username nchar(15),
    @Password nchar(15)
)
AS
    SET NOCOUNT ON;
    DECLARE @cnt INT
    SELECT        @cnt = COUNT(*)
    FROM            Login
    WHERE        (Username = @Username) AND (Pass = @Password)

    RETURN @cnt