March 9, 2009 at 6:22 am
hey all.
I just came across the debugger in SSMS for 2008. I thought this would be a very handy tool, but I cannot seem to get it working properly.
I have a stored procedure which takes in some parameters, one of which is a bit field. In the SP it has an if statement to run if the bit field is '0' or another if statement if it is '1'. But when I set a breakpoint in the SP on the select statement that runs in each if statement, one of them never traps in debug mode. It looks like only one of the if statements is ever run..however after testing, the other if statement actually does show the correct results, but just doesnt debug properly!
A simplified excerpt from my SP:
CREATE PROC [dbo].[udp_searchForAvailableActivityTimeSlots]
@AdminSearch BIT
AS
IF (@AdminSearch = 0)
BEGIN
SELECT 'test'
END
IF (@AdminSearch = 1)
BEGIN
SELECT 'test2'
END
Any ideas?
March 9, 2009 at 12:32 pm
Hey Paul,
When executing code are you clicking on the "Play" button; as that will start debugger. So even if you put break-point debug will not show up unless you have started it in debug mode..
A good intro to debug mode is, http://www.mssqltips.com/tip.asp?tip=1695.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 9, 2009 at 1:02 pm
yes I am in debug mode. I use key F11 to step into the SP from the SP call, and can actually see the little arrow sign on the left which shows me which line is getting executed. And it shows the arrow at the wrong select statement...down at the table definitions i.e. where I put all my table inner joins.
March 9, 2009 at 1:12 pm
Inner Join? The code you posted different from the code you are trying to debug? The code you posted works fine for me even with break points...
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 9, 2009 at 5:59 pm
Hi.
Yes the code getting run is different, that code was put for ease of understanding. Basically, then code has one select statement in each if statement...with multiple joins on tables, but it doesnt make any difference, as it is still just one select statement per if block like the code I posted.
Are you saying if you put a break point on each select statement above, it will stop in both?? If so..Ill maybe look to see if there are any updates for 2008 or something as it definately wasnt breaking where it should...altho the correct Select statement was getting run!
Thanks, Paul.
March 9, 2009 at 6:08 pm
I put a break point on both select statements; so when my bit was 1 it stopped on the first select and bypassed the 2nd. When my bit was 0 it bypassed the first and stopped on the second.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 10, 2009 at 3:02 am
There is defo something wrong with my sql server install or something then...as thats what I would expect, but it doesnt actually happen!
March 10, 2009 at 3:09 am
What key are you using to go through each line? I was using F11
March 10, 2009 at 3:21 am
Here is the full SP definition...just in case there is something wrong with how Im creating teh SP for it not to work.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[udp_test]
@AdminSearch BIT
AS
SET NOCOUNT ON
IF (@AdminSearch = 0)
BEGIN
SELECT 'test1'
END
IF (@AdminSearch = 1)
BEGIN
SELECT 'test2'
END
SET NOCOUNT OFF
March 10, 2009 at 1:56 pm
I wasn't pressing on shortkey ;-).
First time I steped-in using the tool bar button and placed my break points on both SELECT statements.
Then I ran it using Debug mode; and just pressed run. And it stopped on the statement on its own....
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 10, 2009 at 2:34 pm
then i really dont know what im doing wrong
March 10, 2009 at 4:33 pm
I don't know if it is anything wrong .. what version are you doing this on? I am using RC0 .. maybe try reinstalling?
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 10, 2009 at 4:38 pm
its sql server 2008 full version. I have checked microsoft update and its fully up to date
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply