CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3301

Debug CTE with variables?

Many times I want to look check the result of a certain query within a CTE with a @variable without having to alter the whole thing by adding select * from cte at the end.

An example is something like this:

declare @From date
declare @To date
declare @city varchar(20)
declare @st varchar(5)

// Lots of code that sets all the @variables

;with cteSales as
(
    select * from MyTable 
    where 
    From = @From and To = @To and 
    salesman = @salesman and city = @city and st = @st
) 
//HERE GOES A LONG QUERY THAT USES cteSales

The only way I know to debug the query in the CTE is to 1) replace the variables with values and execute the query or 2) comment everything after cteSales and add select * from cteSales.

The latter is less uncomfortable, but both require changing lots of things from the original code.

Is it possible to debug a select stament in a cte without using any of the above two options?

Upvotes: 0

Views: 1741

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

Another option, using your code example, would be to do this:

declare @From date
declare @To date
declare @city varchar(20)
declare @st varchar(5)

// Lots of code that sets all the @variables

--;with cteSales as   --comment these two lines out for testing
--(
    select * from MyTable 
    where 
    From = @From and To = @To and 
    salesman = @salesman and city = @city and st = @st  --highlight the code up to here and execute
) 
//HERE GOES A LONG QUERY THAT USES cteSales

EDIT in response to:

I was thinking that the IDE had some obscure hidden feature that would let me view the results of a cte without having to alter the whole query

In that case, the answer is no. There is nothing like that in the IDE.

Upvotes: 2

Daniel Gimenez
Daniel Gimenez

Reputation: 20589

You can wrap the very long query into a cte as well, then at the bottom all you have to do is comment out one line.

;with cteSales as
(
    select * from MyTable 
    where 
    From = @From and To = @To and 
    salesman = @salesman and city = @city and st = @st
) 
, cteVeryLongQuery as (
    //HERE GOES A LONG QUERY THAT USES cteSales
)
SELECT * FROM cteVeryLongQuery
-- SELECT * FROM cteSales -- Uncomment this for debugging `cteSales` and comment out the line above.

Finally, if you're using SQL Management Studio, use the shortcuts Ctrl+K+C to comment out lines and Ctrl+K+U to uncomment them.

Upvotes: 2

Related Questions