Really wierd Transaction? issue

Question

Really wierd Transaction? issue

tojamismis

SSC Rookie

Points: 31
More actions
October 1, 2004 at 11:31 am

#85609

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...

Viewing 6 posts - 1 through 5 (of 5 total)

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

Paul Clark-165098 SSC Veteran Points: 263 More actions · Answer 1

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

tojamismis SSC Rookie Points: 31 More actions · Answer 2

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.

srv1491 Old Hand Points: 372 More actions · Answer 3

Take a look at SET ANSI_NULL setting in the help. This maybe your problem where <> NULL evaluates to UNKNOWN.

David Burrows SSC Guru Points: 65141 More actions · Answer 4

Plus, it would better written as

IF @NewClosedDetail IS NOT NULL

Far away is close at hand in the images of elsewhere.
Anon.

mwelcome Right there with Babe Points: 739 More actions · Answer 5

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".