August 19, 2008 at 11:50 pm
Dear all,
I need your help on this when i execute the following procedure i'm getting a error like
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
i dont know where i'm doing mistakes so please help me on this
Alter PROC [dbo].[upd_login_password1]
(
@p_login_id INT,
@p_old_login_password varchar(100)=null,
@p_new_login_password varchar(100)=null,
@p_old_transaction_password varchar(100)=null,
@p_new_transaction_password varchar(100)=null
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @l_error_no INT
--BEGIN TRY
BEGIN TRAN
if(@p_new_login_password ='' or @p_new_login_password=null)
BEGIN
--Updation failed.
SET @l_error_no = 2010
SELECT @l_error_no as error_no
Return
END
--Checks for login password
Else if(@p_old_login_password=(select login_password from login_master where login_id=@p_login_id))
BEGIN
--updates login password
UPDATE login_master SET
login_password=@p_new_login_password,
update_user=@p_login_id,
update_date=getdate() WHERE login_id =@p_login_id
--Updated sucessfully
SET @l_error_no = 1010
SELECT @l_error_no as error_no
END
Else
BEGIN
--Updation failed.
SET @l_error_no = 2010
SELECT @l_error_no as error_no
Return
END
if(@p_new_transaction_password ='' or @p_new_transaction_password=null)
BEGIN
rollback tran
--Updation failed.
SET @l_error_no = 2010
SELECT @l_error_no as error_no
Return
END
else if(@p_old_transaction_password=(select transaction_password from login_master where login_id=@p_login_id))
BEGIN
UPDATE login_master SET
transaction_password=@p_new_transaction_password,
update_user=@p_login_id,
update_date=getdate() WHERE login_id =@p_login_id
--Updated sucessfully
SET @l_error_no = 1010
SELECT @l_error_no as error_no
commit tran
END
Else
BEGIN
rollback tran
--Updation failed.
SET @l_error_no = 2010
SELECT @l_error_no as error_no
Return
END
END
Thanks
Chandru.
August 20, 2008 at 12:25 am
First of oll, where are you getting the error?. Can you use some PRINTs to obtain the exact point of the error.
This procedure is calling from another procedure, or from one point in the application where a transaction is opened?
Then, I would try to simplify the procedure, in something like that
1) Verify the input parameters: @p_new_login_password and @p_old_transaction_password, if there is a problem return an error without open any transaction.
2) Get the old values of login_password and transaction_password in only one access. Verify them with the entry parameters, and again, if there is a problem return an error without open any transaction.
3) If all is OK, then Open a transaction
4) Make only one UPDATE to change both fields at the same time
5) Verify @@ERROR and Commit or Rollback the transaction appropriately.
August 20, 2008 at 2:26 pm
I see at least two paths that Begin Tran and then Return.
In your first conditional block if the password parameter is empty string or null you do a return leaving the Transaction open.
In the next set of Checks you have Else if the Old Password Parameter meets some condition. If it does not you again exit with an open transaction (about line #42)
I recommend you do a truth table on your tests. I am sure you could simplify them a lot.
August 20, 2008 at 5:29 pm
NULL will never equal to NULL if you write it like @variable = NULL.
Write it as @variable IS NULL
And what's the exact error?
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply