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:
- Looks like you're storing the password in plain text, which if is the case, is a bad idea
- 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.
- 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
精彩评论