I am using Delphi 2010 and SQL Server 2012.
I have an SP as follows: It checks to see if a given user/password combination exists and returns the UserId if one is found. It also sets the return value to 1 to indicate a user has been found.
ALTER PROCEDURE [dbo].[DoesStandardUserExist]
-- Add the parameters for the stored procedure here
@UserId integer = null output
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select @UserId = Users.Id from Users
where Users.UserId = @Username and Users.Password = @Password and Users.Status = 'A'
-- If the login was found then return 1.
if not( @UserId is null) return 1
else return 0 ;
I have dropped a TADOStoreProc component on my DataModule and use it as follows:
DoesStandardUserExist.Parameters.ParamByName('@UserName').Value := edtUserName.Text ;
DoesStandardUserExist.Parameters.ParamByName('@Password').Value := edtPassword.Text ;
if not VarIsNull( DoesStandardUserExist.Parameters.ParamByName('@UserId').Value ) then
BUT it appears that only the first time i execute the SP i get the correct return values. After the first successful user/password match the values of @UserID and @Return_Value are always set to the last successful match.
They never get set to null (@UserId), or 0 (@Return_value)
The SP works as expected when called directly from Studio Manager. Am i using ADOStoredProc correctly?