T-SQL Debugging

vcbertini
vcbertini used Ask the Experts™
on
I have a stored procedure that does not seem to be working. I have verified that the record exists in the database. I have a feeling that there is some procedural problem I am overlooking, but after hours, I have not been able to find it. HELP.

I am sending in the following parameters:
@ID = 2140
@action = 'S'  (although I really don't need to send this in because it is the default)

I am not sending any other parameters because they are not applicable.  Unfortunately I don't have the means to debug this (unless anyone can recommend a good freeware T-SQL debugger)
@type nchar(2) = 'X',
	   @ID int = 0,
	   @action nchar(2) = 'S',
	   @semester nchar(40) = null
	  
AS
BEGIN

if @action = 'I'
INSERT INTO Advisors
           ([semester])
           
     	VALUES
          (@semester)

ELSE IF @action = 'U'

	UPDATE	Advisors
	SET		semester = @semester		
	WHERE		ID = @ID

ELSE IF @action = 'D'

	DELETE FROM Advisors WHERE ID = @ID

ELSE IF @action = 'S'
	IF @ID = 0
		IF @type = 'X'
			SELECT * FROM Advisors
			WHERE (semester = @semester)
			ORDER BY lname, fname
		ELSE IF @type = 'A'
			SELECT * FROM Advisors
			WHERE (semester = @semester) AND approved = 1
			ORDER BY lname, fname
		ELSE IF @type = 'P'
			SELECT * FROM Advisors
			WHERE (semester = @semester) AND approved = 0
			ORDER BY lname, fname		
	ELSE

		SELECT     *
		FROM         Advisors
		WHERE     (ID = @ID)

END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,

I think you need a begin ... end in the last if statement. Try this:

/peter
@type nchar(2) = 'X',
           @ID int = 0,
           @action nchar(2) = 'S',
           @semester nchar(40) = null
          
AS
BEGIN

if @action = 'I'
INSERT INTO Advisors
           ([semester])
           
        VALUES
          (@semester)

ELSE IF @action = 'U'

        UPDATE  Advisors
        SET             semester = @semester            
        WHERE           ID = @ID

ELSE IF @action = 'D'

        DELETE FROM Advisors WHERE ID = @ID

ELSE IF @action = 'S'
        IF @ID = 0 BEGIN
                IF @type = 'X'
                        SELECT * FROM Advisors
                        WHERE (semester = @semester)
                        ORDER BY lname, fname
                ELSE IF @type = 'A'
                        SELECT * FROM Advisors
                        WHERE (semester = @semester) AND approved = 1
                        ORDER BY lname, fname
                ELSE IF @type = 'P'
                        SELECT * FROM Advisors
                        WHERE (semester = @semester) AND approved = 0
                        ORDER BY lname, fname           
        END ELSE

                SELECT     *
                FROM         Advisors
                WHERE     (ID = @ID)

END

Open in new window

Just by the looks, it seems you need a begin...end statement in each if (except the first). I think this because there's no such thing as a elseif in T-SQL, so the IF is the only instruction that will execute for each else. Someone correct me if my logic is wrong here.

Give it a try and let us know.

Author

Commented:
That did it! Do you always need a new "BEGIN/END" in a third level nested-if?  
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Last comment was directed @pivar

Commented:
The last ELSE after the END will be interpreted as ELSE to the deepest level IF if you don't supply the END. You have to show which IF-block it's connected to.

Commented:
By encapsulating the IF-block with BEGIN ... END you show where it ends.
Your code was perfectly legal if  

"SELECT     *
FROM         Advisors
WHERE     (ID = @ID)"

should be executed if @type was not 'X', 'P' or 'A'.
But here it should be executed if @ID wasn't 0.
I'm not sure if you intended to award the points to me, but IMHO I think you should split these points if I'm going to be considered in the accepted answers.

I think pivar was the one who hinted at the solution. Are you sure about this?

Author

Commented:
Oops, yes you're right. How do I go change that?
You should ask a mod to re-open the question so you can close it and assign the points accordingly.
I think you can use the "Request attention" button/link.

Author

Commented:
Sorry about the first point-assignment mix up. I confused pivar's response for my initial inquiry. Thanks to everyone for their prompt help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial