CODEWITHSUNDEEP
sql-servert-sqlvariablesvariable-declaration
ailerifren
ailerifren

Reputation: 97

How to check if a sql variable is declared?

In MS SQL Server. Please don't tell me to ctrl-f. I don't have access to the entire query, but I'm composing the columns that depend on whether certain variable is declared.

Thanks.

Edit: I'm working with some weird query engine. I need to write the select columns part and the engine will take care of the rest (hopefully). But in some cases this engine will declare variables (thankfully I will know the variable names), and in other cases it doesn't. I need to compose my columns to take these variables when they are declared, and give default values when these variables are not declared.

Upvotes: 2

Views: 2965

Answers (1)

swasheck
swasheck

Reputation: 4703

Given the limitations of my understanding of what you're running (I'm deciphering the word problem to mean that your "query engine" is actually a "query generation engine" so, something like an ORM) you could observe what's occurring on the server in this scenario with the following query:

select 
    sql_handle, 
    st.text
    from sys.dm_exec_requests r 
    cross apply sys.dm_exec_sql_text(r.sql_handle) st
where session_id <> @@SPID
    and st.text like '%@<<parameter_name>>%';

The statement needs to have begun execution to be able to catch it. Depending on a multitude of situations, you may be able to pull it from query stats, too: This will also get you the query plan (if it has one), but note that it will also pull stats for itself as well as the query above so you'll need to be discerning when you look at the outer and statement text values:

select 
    text,
    SUBSTRING(
                st.text,
                (qs.statement_start_offset / 2) + 1,
                ((CASE qs.statement_end_offset
                                    WHEN -1 THEN DATALENGTH(st.TEXT)
                        ELSE qs.statement_end_offset
                        END -
                                qs.statement_start_offset) / 2) + 1)
                    AS statement_text,
                    plan_generation_num, creation_time, last_execution_time, execution_count
                    ,total_worker_time, last_worker_time, min_worker_time, max_worker_time, 
                    total_physical_reads, min_physical_reads, max_physical_reads, last_physical_reads,
                    total_logical_writes, min_logical_writes, max_logical_writes, last_logical_writes,
                    total_logical_reads, min_logical_reads, max_logical_reads, last_logical_reads,
                    total_elapsed_time, last_elapsed_time, min_elapsed_time, max_elapsed_time, 
                    total_rows,last_rows,min_rows,max_rows
                    ,qp.*

    from sys.dm_exec_query_stats qs 
    cross apply sys.dm_exec_sql_text(qs.sql_handle) st
    outer apply sys.dm_exec_query_plan(qs.plan_handle) qp
    where st.text like '%@<<parameter_name>>%';

Upvotes: 1

Related Questions