Query Temp Table in Stored Proc Whilst Debugging in SQL 2008 Management Studio

Query temp table in stored proc whilst debugging in SQL 2008 Management Studio

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.

How do I inspect table variables and temporary tables from within a debugging session in SSMS 2008?

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.

How to see the values of a table variable at debug time in T-SQL?

That's not yet implemented according this Microsoft Connect link:
Microsoft Connect

View temporary table`s data when debugging an MS SQL Function

One possible solution, that may not be the best, is to:

• Create a permanent table that is the same as the temporary table
• Modify the function so that it dumps the data from the temporary table into the permanent table at the point where the temp table contains the data you're interested in seeing

When the function ends, open up the new permanent table and you'll have a copy of the temporary table's state.

This requires that you have permission to create new tables and modify the function.

Debugging stored procedure in SQL Server 2008 Management Studio

Just write a query that correctly invokes your SP and debug that:

exec MySP p1 p2 etc

In SSMS can I select against temp tables created in the SPROC I'm debugging while the debugger is paused

From CREATE TABLE

Temporary Tables

You can create local and global
temporary tables. Local temporary
tables are visible only in the current
session; global temporary tables are
visible to all sessions.

You will notice from SSMS, that each tab has a different session id, so, I dont think this is possible, unless you make these global.

Debugging Stored Procedure in SQL Server 2008

Well the answer was sitting right in front of me the whole time.

In SQL Server Management Studio 2008 there is a Debug button in the toolbar. Set a break point in a query window to step through.

I dismissed this functionality at the beginning because I didn't think of stepping INTO the stored procedure, which you can do with ease.

SSMS basically does what FinnNK mentioned with the MSDN walkthrough but automatically.

So easy! Thanks for your help FinnNK.

Edit:
I should add a step in there to find the stored procedure call with parameters I used SQL Profiler on my database.

how to select a temp table in database when debugging?

insert into ##temp1 select * from TableName
select * from ##temp1

Explanation:

We need to put "##" with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table:

CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

insert into ##NewGlobalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:

select * from ##NewGlobalTempTable

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

Debugging stored procedures in SQL Server Management Studio

Only with SQL 2008 and SSMS 2008. Select from menu 'Debug\Start Debugging' or press Alt+F5 and will launch the step by step T-SQL debugger.

On 2005 the only way is to attach Profiler and monitor for the SP:StmtCompleted event, which is not exactly debugger step-by-step, but at least you'll see the execution flow. Not to be done on a production machine, obviously.

---

Related Topics