Is there a DEBUG switch in sql server to inclue debug info in query results?

I am writing a quite complex set of views that I will use for creating some very complex select statments. To avoid making mistakes I included in the views both the ID of some fields and the DESCRIPTION. The DESCRIPTION is need only for debugging, because I don't need it in the final query. Of course everything works, but to improve performance I would like to being easily able to include/remove the descriptions, to avoid to maintain 2 big set of queries (one for debug and one for the real one).

So i was wondering if it were possible to have something like:

select
  FieldA,
  FieldB #ifdef DEBUG,
  FieldC,
  FieldD #endif

so that if the DEBUG switch is on I get:

select
  FieldA,
  FieldB,
  FieldC,
  FieldD

开发者_C百科otherwise I get:

select
  FieldA,
  FieldB 

This is typical of compilers, but anyway is there a technique to simulate this effectively in T-sql?

---

From the toolbox:

One of the feature in the free SSMS Tools Pack is to integrate Racerx's solution into SSMS. With it, you can create so-called debug sections, similar to Racerx's example. You then decide via Combobox, which version of the script you want to execute: Debug or "Release" which is with or without debug-sections respecively.

Looks like this:

select
  FieldA,
  FieldB
#-BeginDebug Put Description Here
  ,
  FieldC,
  FieldD
#-EndDebug
FROM t

---

NO, and for a good reason.

when you write a procedure with a query:

select   FieldA,   FieldB,   FieldC,   FieldD ...

the optimizer looks at a bunch of things to build a query plan, which includes the columns you are returning. If the columns change:

select   FieldA,   FieldC ....

the query plan can change as well. If ALL of the columns returned are in the index, then the query never actually go to the table (see covering index). Other items to affect a query plan are available indexes, statistics held on the table and index, and which and how you filter (where and join) in the query. Throw an OR in there and you can sometimes kill the performance.

The goal with SQL is to have it written so it runs fast, not so it is not redundant and/or easy to maintain from a coder's perspective. You can easily and drastically alter a query's efficiency by making trivial changes. Things like while x=1 begin... end translate easily and predictably to machine code. However, the many optimizations used when converting select x from ... join .. join.. are difficult to predict even for advanced developers. these optimizations can even change with data changes.

if you really want something like this you' need to do the following:

1) write your procedures like this:

select
  FieldA,
  FieldB--||DEBUG||--,
  --||DEBUG||--FieldC,
  --||DEBUG||--FieldD

2) work on all your procedures as text files (not just procedure objects in SSMS)
3) use a search and replace utility to either include --||DEBUG||-- comment (remember anything on the same line after -- is commented out) or replace it as an empty string.
4) you can then run the procedure scripts with or without the debug info.

---

You can add @debug parameter in stored procedure. Your statement will look like this:

IF @Debug=0
SELECT FiledA, FieldB
ELSE
SELECT FiledA, FieldB,FieldC,FieldD