May 6, 2009 at 9:05 am
I am a newbie. Having problems creating a login procedure. I keep getting this error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Any help is greatly appreciated. Thanks.
ALTER PROCEDURE [dbo].[sp_qcentricLogin]
(
-- Add the parameters for the stored procedure here
@userid varchar(MAX),
@password varchar(MAX),
@results INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @exists INT
DECLARE @err INT
BEGIN
-- find user with userID and password
SELECT @exists = 1 FROM USER_TBL WHERE userID = @userid AND password = @password
SELECT @err = @@error IF @err <> 0 RETURN @err
-- Insert if user does not exist on the LOGIN_TBL
BEGIN TRANSACTION
IF @exists = 0 BEGIN
INSERT INTO LOGIN_TBL values (@userID, 1, GETDATE(), null, null)
SELECT @err = @@error
IF @err <> 0
BEGIN ROLLBACK TRANSACTION
RETURN @err
END
ELSE
RETURN @err
END
END
END
May 6, 2009 at 9:18 am
You do not have COMMIT after BEGIN TRANSACTION! You have ROLLBACK but not COMMIT. See it in the attached file and use use indent style in your code!!
Alex Prusakov
May 6, 2009 at 9:26 am
Thank you very much! That was a problem. Here is the entire listing. I am getting the message again once I added the [ELSE] condition. ???
ALTER PROCEDURE [dbo].[sp_qcentricLogin]
(
-- Add the parameters for the stored procedure here
@userid varchar(MAX),
@password varchar(MAX)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @exists INT
DECLARE @err INT
BEGIN
-- find user with userID and password
SELECT @exists = userID FROM USER_TBL WHERE userID = @userid AND password = @password
SELECT @err = @@error IF @err 0 RETURN 1
-- Insert if user does not exist on the LOGIN_TBL
IF (@exists = 0)
BEGIN TRANSACTION
INSERT INTO LOGIN_TBL values (@userID, 1, GETDATE(), null, null)
SELECT @err = @@error
IF @err 0
BEGIN ROLLBACK TRANSACTION
RETURN 2
END
ELSE
RETURN 0
COMMIT TRANSACTION
SELECT @err = @@error IF @err 0 RETURN @err
ELSE
BEGIN TRANSACTION
UPDATE LOGIN_TBL SET statusID = 1 WHERE userID = @userid
SELECT @err = @@error
IF @err 0
BEGIN ROLLBACK TRANSACTION
RETURN 3
END
ELSE
RETURN 0
COMMIT TRANSACTION
SELECT @err = @@error IF @err 0 RETURN @err
END
END[/code]
May 6, 2009 at 10:35 am
This happens most likely because you have:
IF
...
SELECT @err = @@error IF @err 0 RETURN @err
ELSE
Compiler doesn't understand what is this ELSE for. You have to use:
IF
BEGIN
...
SELECT @err = @@error IF @err 0 RETURN @err
END
ELSE
Alex Prusakov
May 6, 2009 at 12:18 pm
The problem is that in you ELSE clauses you are RETURN-ing before you get to your COMMIT. You will need to write the ELSE's like this:
ELSE
Begin
COMMIT
RETURN 2
End
Also, there are several places where you have multiple statements after an IF or an ELSE, but no BEGIN..END around them. Your procedures's logic cannot work right without these.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 1:06 pm
Thank you and Alexander! Found some logical errors too. I think i've got it now based on your feedback.
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @exists INT
DECLARE @err INT
BEGIN
-- determine if user exists
IF (SELECT COUNT(*) FROM USER_TBL WHERE userID = @userid AND password = @password) <= 0
RETURN 1
-- Insert if user does not exist on the LOGIN_TBL
SELECT @exists = COUNT(*) FROM LOGIN_TBL WHERE userID = @userid
IF @exists = 0
BEGIN
INSERT INTO LOGIN_TBL values (@userID, 1, GETDATE(), null, null)
SELECT @err = @@error
IF @err 0
BEGIN ROLLBACK TRANSACTION RETURN 2 END
ELSE
COMMIT RETURN 0
END
ELSE IF @exists = 1
BEGIN
UPDATE LOGIN_TBL SET statusID = 1 WHERE userID = @userid
SELECT @err = @@error
IF @err 0
BEGIN ROLLBACK TRANSACTION RETURN 3 END
ELSE
COMMIT RETURN 0
END
ELSE
BEGIN
RETURN 4
END
END
END
May 6, 2009 at 1:17 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply