1

This is something I am just interested in knowing if it is possible.

Could I write an SQL query that could return the text of itself as a variable?

Where I might use this would be on things like reports that I might want to return a variable that saves the actual query that ran to generate the report, or as a debugging tool.

I have seen there is:

sys.dm_exec_text_query_plan

that would return the text of the query but can't see an easy way to always get the currently executing queries ID to link back to this table.

Has anyone ever written something to do something like this?

I am using SQL Server 2008, 2012 and 2014.

4

There is no way that a query can output its own text, that I am aware of, but you can certainly capture it using sys.dm_exec_requests.

You can try this:

-- Query to Run
SELECT 100 / 10

-- Query Text Capture
DECLARE @Query NVARCHAR( MAX )
SET @Query = ( SELECT text /*, query_plan*/
    FROM sys.dm_exec_requests AS er
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS et
        CROSS APPLY sys.dm_exec_text_query_plan( er.plan_handle, DEFAULT, DEFAULT ) AS ep
    WHERE session_id = @@SPID );
SELECT @Query

Or inside SP:

CREATE PROCEDURE Test
AS
    SET NOCOUNT ON

    SELECT 100 / 10

    DECLARE @Query NVARCHAR( MAX )
    SET @Query = ( SELECT text /*, query_plan*/
        FROM sys.dm_exec_requests AS er
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS et
            CROSS APPLY sys.dm_exec_text_query_plan( er.plan_handle, DEFAULT, DEFAULT ) AS ep
        WHERE session_id = @@SPID );

    SELECT @Query
GO
EXEC Test

I think you would be better served by SQL Trace though.

For a procedure, you can use sp_helptext and equivalents to get its text. I don't think you would need to capture SP text at the point execution as SPs don't get redefined often. Capturing SP name should be enough.

0

Thanks Alex I was able to use what you had written to make a query that will do what I want. This should let me implement my own preprocessor logic.

if(1 = 1)
BEGIN
    DECLARE @Query NVARCHAR( MAX )
    SET @Query = ( SELECT text /*, query_plan*/
    FROM sys.dm_exec_requests AS er
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS et
        CROSS APPLY sys.dm_exec_text_query_plan( er.plan_handle, DEFAULT, DEFAULT ) AS ep
    WHERE session_id = @@SPID );
    SELECT @Query
    SET @Query = REPLACE(@Query, '1 = 1' , '1 <> 1')
    SET @Query = REPLACE(@Query, '--DEBUG', '')
    EXEC sp_executesql @statement = @Query
END
ELSE
BEGIN
    --DEBUG RAISERROR ('The Begining', 11, 1)WITH NOWAIT    
    /* Add the main query here */
    SELECT 100/10 
    --DEBUG RAISERROR ('The End', 11, 1)WITH NOWAIT 
END

Again this is just to make developing and testing code easier, and not for using in production.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.