20

I have a really large stored procedure which calls other stored procedures and applies the results into temp tables.

I am debugging in SQL 2008 Management Studio and can use the watch window to query local parameters but how can I query a temp table on whilst debugging?

If its not possible is there an alternative approach? I have read about using table variables instead, would it be possible to query these? If so how would I do this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
DevOverflow
  • 491
  • 1
  • 5
  • 16
  • Dup of http://stackoverflow.com/questions/1900857/how-to-see-the-values-of-a-table-variable-at-debug-time-in-t-sql – Schultz9999 Sep 11 '12 at 21:15

4 Answers4

18

Use global temporary tables, i.e. with double hash.

insert into ##temp select ...

While debugging, you can pause the SP at some point, and in another query window, the ## table is available for querying.

select * from ##temp

Single hash tables (#tmp) is session specific and is only visible from the session.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
15

I built a procedure which will display the content of a temp table from another database connection. (which is not possible with normal queries). Note that it uses DBCC PAGE & the default trace to access the data so only use it for debugging purposes.

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
  • 1
    This works great and it does not require modifications to existing stored procedures! If only I could upvote 10x! – Colin Jul 16 '14 at 20:00
  • 1
    This is the best answer, if you have full access to add these helper procs and run in Master db – Mike M May 25 '15 at 12:31
1

an alternative would be to use a variable in your stored proc that allows for debug on the fly.

i use a variable called @debug_out (BIT).

works something like this

ALTER PROCEDURE [dbo].[usp_someProc]

@some_Var VARCHAR(15) = 'AUTO',

@debug_Out BIT = 0

BEGIN

  IF @debug_Out = 1
       BEGIN
            PRINT('THIS IS MY TABLE');
            SELECT * FROM dbo.myTable;
       END ................

END

the great thing about doing this is when your code launches your stored procedure, the default is to show none of these debug sections. when you want to debug, you just pass in your debug variable.

EXEC usp_someProc @debug_Out = 1

jboby
  • 41
  • 1
0

simply dont drop temp table or close transaction

eg 

select * into #temp from myTable

select * from #temp
Nezam
  • 4,122
  • 3
  • 32
  • 49
Bonshington
  • 3,970
  • 2
  • 25
  • 20