Really wierd Transaction? issue
-
Hi all...
I'm experiencing an issue that I really do not understand.
I have the following PROC. The issue is... It was not completing the last SQL statement (in the IF @NewClosedDetail <> NULL branch), until (this is the wierd part) I added the line in RED.
This line is just a SELECT statement to output text for debugging purposes as I ran the transaction. (SELECT 'Right Before Begin')
If I comment out or remove the line, the SQL statement inside does not run. If I leave it, the statement works just fine.
Has anyone else seen this behavior?
CREATE PROCEDURE sp_ORLCloseDefect
@CurrDefectID int,
@NewProjectNum int,
@NewDescription varchar(100),
@NewSeverity varchar(25),
@NewTestCase varchar(50),
@NewStatus varchar(25),
@NewDetail varchar(8000),
@NewPackager tinyint,
@NewDateClosed datetime,
@NewCloseAction varchar(50),
@NewClosedDetail varchar(4000)
AS
DECLARE @numFound int
BEGIN TRAN T1
UPDATE defects SET project_id = @NewProjectNum, description = @NewDescription,
sev_no = @NewSeverity, procedure_type = @NewTestCase, status = @NewStatus,
pkgr_id = @NewPackager, date_closed = @NewDateClosed, close_action = @NewCloseAction
WHERE defect_id = @CurrDefectID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION T1
SELECT 'An Unknown Error Occurred During the Edit Defect Stored Procedure'
RETURN(99)
END
SELECT @numFound = count(*) FROM test_detail WHERE defect_id = @CurrDefectID
IF @numFound > 0
BEGIN
UPDATE test_detail SET data = @NewDetail
WHERE defect_id = @CurrDefectID
END
ELSE
BEGIN
INSERT INTO test_detail(defect_id, data) VALUES(@CurrDefectID, @NewDetail)
END
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION T1
SELECT 'An Unknown Error Occurred During the Edit Defect Stored Procedure'
RETURN(99)
END
IF @NewClosedDetail <> NULL
SELECT 'Right Before Begin' --************************ ISSUE IS HERE
BEGIN
SELECT @numFound = count(*) FROM closed_detail WHERE defect_id = @CurrDefectID
IF @numFound > 0
BEGIN
UPDATE closed_detail SET closed_data = @NewClosedDetail
WHERE defect_id = @CurrDefectID
END
ELSE
BEGIN
INSERT INTO closed_detail(defect_id, closed_data) VALUES(@CurrDefectID, @NewClosedDetail)
END
END
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION T1
SELECT 'An Unknown Error Occurred During the Edit Defect Stored Procedure'
RETURN(99)
END
COMMIT TRAN T1
GO
OK...
-
The variable @NewClosedDetail is not referenced between the declaration and the IF statement. Since the value has never been set, it is always NULL in the procedure
-
Actually, one of my other debugging maneuvers was to put a line of
SELECT @NewClosedDetail
Right above the if statement and it was correctly outputting the value passed to the stored procedure.
-
Take a look at SET ANSI_NULL setting in the help. This maybe your problem where <> NULL evaluates to UNKNOWN.
-
Plus, it would better written as
IF @NewClosedDetail IS NOT NULL
Far away is close at hand in the images of elsewhere.
Anon. -
The issue here is actually quite simple
. When you do an if statement it applies to either:1. The statement immediately following the if
2. A group of statements within a begin end block if they immediately follow the if
All this means in your case when you put your "debug" code in, the code in the begin end block becomes non-conditional since it is no longer the first statement following the if. It will execute every time.
The problem here is that testing for nulls (at least since SQL Server 6.5) should not be done using = and <>. It should be done with "Is Null" or "Is not Null".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply