Link to home
Create AccountLog in
Avatar of vcbertini
vcbertiniFlag for United States of America

asked on

T-SQL Debugging

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

ASKER CERTIFIED SOLUTION
Avatar of pivar
pivar
Flag of Sweden image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Avatar of Member_2_861731
Member_2_861731
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of vcbertini

ASKER

That did it! Do you always need a new "BEGIN/END" in a third level nested-if?  
Last comment was directed @pivar
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.
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?
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.
Sorry about the first point-assignment mix up. I confused pivar's response for my initial inquiry. Thanks to everyone for their prompt help.