Problem in stored procedure result

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 properl开发者_如何学Goy, is that possible ?

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.

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)

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