Avatar of vcbertini
vcbertini
Flag 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

Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
vcbertini

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
pivar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Member_2_861731

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
vcbertini

ASKER
That did it! Do you always need a new "BEGIN/END" in a third level nested-if?  
vcbertini

ASKER
Last comment was directed @pivar
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
pivar

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

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?
vcbertini

ASKER
Oops, yes you're right. How do I go change that?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Member_2_861731

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

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