Possible to get the line number of the currently executing sproc in SQL Server?

A few years back I worked in a Sybase/Delphi environment, using the BDE to connect to the DB server. We had a little Delphi app that, given the name of a currently executing stored procedure, could tell you what line of that sproc was currently being executed. This was exceptionally useful for debugging sprocs that seemed to be hanging.

I'd like to use this functionality in SQL Server, but I can't remember whether it was a Sybase or a BDE feature. Is this functionality available in SQL Server and if so, what command(s) do I need to use to vie开发者_运维百科w this information?

---

You can use something like

SELECT
    CASE
        WHEN statement_end_offset = -1
        THEN text
        ELSE SUBSTRING(text,statement_start_offset/2,(statement_end_offset- statement_start_offset)/2)
    END,
    statement_end_offset, statement_start_offset
FROM    sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE session_id = 53 --Or whatever!

The statement_start_offset and statement_end_offset are character offsets that map to the statement currently being executed rather than a line number.

A single line can contain multiple executable statements and a single statement can span multiple lines.

---

If you are using SQL Server 2008, you can debug stored procs much like you can debug C# code. You can set break points and execute statement by statement. You can do this from within SSMS.