debugging stored procedure in MS SQL server 2008

Question

I have a stored procedure and I'm trying to debug it in the query window... when I open the stored procedure, I set some breakpoints in the code, but they never get hit.. In the code below, I can step through the USE line, then set ANSI_NULLS ON and then the code just says commands executed successfully, even though I have breakpoints set all throughout the code below it. Am I missing something?!

USE [Tool1]
GO
/****** Object:  StoredProcedure [dbo].[aspdnsf_ImportProductPricing_XML]    Script Date: 10/02/2014 09:38:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Alter proc [dbo].[aspdnsf_ImportProductPricing_XML]
    @pricing ntext

AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #tmp (ProductID int, VariantID int, KitItemID int, Name nvarchar(400), KitGroup nvarchar(800), SKU nvarchar(50), SKUSuffix nvarchar(50), ManufacturerPartNumber nvarchar(50), Cost money, MSRP money, Price money, SalePrice money, Inventory int)
DECLARE @hdoc int, @retcode int
EXEC @retcode = sp_xml_preparedocument 
                    @hdoc OUTPUT,
                    @pricing

INSERT #tmp
SELECT *
FROM OPENXML(@hdoc, '/productlist/productvariant', 2) 
        WITH (ProductID int, VariantID int, KitItemID int, Name nvarchar(400), KitGroup nvarchar(800), SKU nvarchar(50), SKUSuffix nvarchar(50), ManufacturerPartNumber nvarchar(50), Cost money, MSRP money, Price money, SalePrice money, Inventory int)


UPDATE dbo.ProductVariant
SET Price = t.Price,
    SalePrice = nullif(t.SalePrice,0),
    Inventory = t.Inventory,
    Cost = t.cost
FROM dbo.ProductVariant p 
    join #tmp t  on p.ProductID = t.ProductID and p.VariantID = t.VariantID
WHERE KitItemID = 0


UPDATE dbo.KitItem
SET PriceDelta = t.Price
FROM dbo.KitItem k
    join #tmp t  on k.KitItemID = t.KitItemID
WHERE t.KitItemID > 0



exec sp_xml_removedocument @hdoc

DROP TABLE #tmp
END

Debug is pretty useless in SQL Server stored procedures, especially when #temp tables are involved. Have you considered good old fashioned PRINT and SELECT? — Aaron Bertrand
– Aaron Bertrand, Commented Oct 2, 2014 at 14:02

Bogdan Bogdanov · Accepted Answer · 2014-10-02 14:03:33Z

1

When you debug store procedure you have to prepare exec statement for that procedre. You set breakpoint on it and then use Run button to start debugging. When you step into the procedure new window will be opened. In that window set breakpoints. They will work.

answered Oct 2, 2014 at 14:03

Bogdan Bogdanov

1,7232 gold badges21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Philip Kelley Over a year ago

Is it possible to view the contents of temporary tables "at breakpoints" when debugging?

Bogdan Bogdanov Over a year ago

@PhilipKelley In DEBUG you can see flow of the procedure and local variables. To see content of a table you have to use temporary SELECT statements. So from that point I agree with @AaronBertrand comment. But if you need DEBUG a trigger which triggers another trigger it is sometimes useful to use SSMS DEBUG.

user3267755 Over a year ago

This worked perfectly, in hindsight I feel pretty silly trying to debug an ALTER statement.

Philip Kelley · Accepted Answer · 2014-10-02 14:31:04Z

I gave up on debug in SQL years ago, and am sad to hear it has not improved in even the most recent versions. Here’s an overview of the tatcic I use when debugging stored procedures, which may or may not help you.

First, cut-and-paste the code to a new “working” window.

Run any necessary special formatting commands, e.g.

USE [Tool1]
GO
/****** Object:  StoredProcedure [dbo].[aspdnsf_ImportProductPricing_XML]    Script Date: 10/02/2014 09:38:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Next, comment out everything down through the create (or alter) procedure statement, like so:

--USE [Tool1]
--GO
--/****** Object:  StoredProcedure [dbo].[aspdnsf_ImportProductPricing_XML]    Script Date: 10/02/2014 09:38:17 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO


--Alter proc [dbo].[aspdnsf_ImportProductPricing_XML]
--    @pricing ntext

If there are parameters, plop down a DECLARE statement in front of them, and remove any output settings.

Replace the AS statement with SET values for each parameter, and use these to set testing values

From there, scroll down through a portion of code, highlight that point and all preceding text (shift + control + home), and execute (F5), and observe what happens. Insert PRINT and SELECT statements and repeat as necessary, slowly increasing how much code gets executed with each call, or when possible running only a few selected lines of code.

There are of course a zillion gotchas to this (which is why it would be really nice to have real world debug in SQL—and I don’t see this happening, in part because of @Temp tables, and in part because of BEGIN TRANSACTION / EXECUTE / GOTO lunch / ROLLBACK), but I digress. One such gotcha here is your #Temp table. On the first pass it gets created, and on all subsequent passes you’ll get a “table already exists” error. Lazy (because it doesn't work the first time you run it) work-around:

DROP TABLE #tmp
CREATE TABLE #tmp (ProductID int, <etc>)

Fussy work-around:

IF object_id('tempdb..#Tmp') is not null
    DROP TABLE #tmp

CREATE TABLE #tmp (ProductID int)

sp_xml_preparedocument / sp_xml_removedocument is going to be another gotcha. Not sure how to deal with that, we gave that up for Xquery years ago.

Be extremely careful about using local variables vs. parameters.

Collectives™ on Stack Overflow

debugging stored procedure in MS SQL server 2008

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related