How to execute sql query when debugging a stored procedure

Question

I'm debugging a stored procedure on SQL Server 2008 and I have this:

INSERT INTO #tempTable (ID, Name)
  SELECT ID, Name FROM dbo.MYTABLE WHERE dbo.MYTABLE.Old >= 15

How can I view the data into #tempTable on Debug time?

Answer 1

In SQL Server Management Studio, you can't execute query directly while debugging stored procedure, and that's still not implemented(I think). You can only view the local variables value in Local Debug Window.

There are some work around to see temp table values while in Debugging mode:-

1) In the stored procedure, after insert data into #temptable, add this line of code to get temptable values in xml table varriable where you want to see temptable values. Then you can check the values in Local Debug window in xml format

  --inserting data into temp table
  INSERT INTO #tempTable (ID, Name)
  SELECT ID, Name FROM dbo.MYTABLE WHERE dbo.MYTABLE.Old >= 15

  --to see records of temp table
  DECLARE @temptable XML 
  SET @temptable = (SELECT * FROM #@temptable FOR XML AUTO)

2) You can convert local temp table(#temptable) to global temptable(##temptable), so when you insert date in temp table, you can open new query window, and able to see global temp table records using select query.