How to check if a sql variable is declared?

Question

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.

You can't conditionally declare a variable in a script so I don't understand what you're trying to do here. — Paul Hunt
– Paul Hunt, Commented Jan 2, 2014 at 22:33
What you are asking does not make sense. Are you trying to reverse engineer a stored procedure, trigger or function that you do not have the source code for? Views do not have variables. I suggest you ask your admin for the T-SQL. — CRAFTY DBA
– CRAFTY DBA, Commented Jan 2, 2014 at 22:36
@addy2601 yah.. that's like the last thing I want to do. But seems like no better solution. Edit: Whoooops, even try/catch can't work. — ailerifren
– ailerifren, Commented Jan 2, 2014 at 22:46
@addy2601: "Not a good thing to do" isn't the issue. The issue is it will have no effect, as indeed the OP has found out. A script with an invalid variable reference just won't run for the try/catch block to be able to catch the error. — Andriy M
– Andriy M, Commented Jan 3, 2014 at 9:57

swasheck · Accepted Answer · 2014-01-02 22:58:02Z

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>>%';

Collectives™ on Stack Overflow

How to check if a sql variable is declared?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related