Stored Proc doing something I didnt think was possible!

Question

Post reply

Stored Proc doing something I didnt think was possible!

Dave Morrison

SSCrazy

Points: 2077
More actions
December 15, 2009 at 3:11 am

#103865

Hi all, I have a VERY strange issue.
I have a simple stored procedure (below) that is used to increment the number of login attempts a user has made. It is used by a PHP application (yes I know PHP and MS SQL dont play nicely together) to manage the number of failed login attempts.
Now it works fine in management studio and from the PHP app on our dev version of the site but on the live version it works fine in management studio but when called from the PHP app on live it is trying to put NULL into the attempts field when you pass the outcome of 1 (i.e. a sucessful login attempt). Now it works fine if you pass an outcome of 0 in that it increments the number of attempts quite happily.
Now I've boot strapped it in that I initialise the the @NewAttempts variable to 0 so I can see no possible way that NULL can be getting into this field.
I'm thinking it must be a connection option or somehting as it works fine on dev but not live (we've also connected the live site to the DEV db and it produces the same error)
I know this is a seudo PHP error but any and all help will be greatly apperciated!
SP code below:
ALTER PROCEDURE [login_attempt]
@user-id int,
@Outcome bit -- 0 Failed, 1 Sucessful
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewAttempts smallint
set @NewAttempts = 0
if @Outcome = 0
begin
select @NewAttempts = hua.attempts + 1 from hub_user_auth as hua
where hua.id = @user-id
end
else
begin
set @NewAttempts = 0
end
update hua
set hua.attempts = @NewAttempts
from hub_user_auth as hua
where hua.id = @user-id
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

I know this doesn't answer your original question, but why don't you change the procedure to something like this?

if @Outcome = 0

update hub_user_auth SET attempts = attempts + 1

where id = @user-id

ELSE

update hub_user_auth SET attempts = 0

where id = @user-id

or this, using a case statement:-

update hub_user_auth SET attempts =

CASE WHEN @outcome = 0 THEN attempts + 1 ELSE 0 END

where id = @user-id

Lowell SSC Guru Points: 323518 More actions · Answer 2

i'd say your table's attempts column was not initialized to zero, but instead has nulls:

select @NewAttempts = hua.attempts + 1 from hub_user_auth as hua

where hua.id = @user-id

would probably be better if it was

select @NewAttempts = ISNULL(hua.attempts,0) + 1 from hub_user_auth as hua

where hua.id = @user-id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Morrison SSCrazy Points: 2077 More actions · Answer 3

Thanks for the replies guys but it was something totally different. The developer had another bit of code that ran after calling my SP that was causing this issue and he was misinterpreting the error returned

*runs of to find something suitably painful to whack the developer with* 😀

Cheers all the same

Dave