How many times have you been

frustrated with debugging your script because of an error? Would you like to

see where the problem is without trying to decipher the error message? I have

found that as I pass more and more variables into my scripts and move toward

almost exclusively using strings, I have incorporated a trick I learned at a

former employer. My manager (Bill Meck) is an “SQL Guru” (even though he

wouldn’t admit it), taught me how to make the script give a return no matter

what situation your variables are in. By using a variable he called @RunMode we

can enable the script to run in any situation. (During development excepted of

course.) Most importantly, it allows for the printing of the SQL statement for

debugging. The @RunMode variable is simply an integer variable that is set to

one of three values. (0, 1 or 2) When set to 0; the query string has “and 1 = 0” added to it: Causing a result set that

consists of only column headers. When set to 1 the script will print the query

only. And finally setting the variable to 2 will cause the query to execute

normally.

To build the script I have

developed a template that I begin with. (insert Template Script) This allows me

to have a faster start and a way to produce multiple uniform procedures. The declaration

of a variable @SQL as VARCHAR(8000) allows us to have a large query string. We can

then dynamically control what the query string looks like through the use of

“If” statements. As they are executed, they will either add to the @SQL string

or exit.

There are some things to remember

with dynamically building a query string in SQL though. First and most

important is that all statements must have a single quote beginning and end.

The string will look like this:

DECLARE @SQL  VARCHAR(8000)
SET @SQL ='select LastName, substring(FirstName, 1,1) As Emp_Name from
Employees'

EXECUTE(@SQL)

Next you should remember that quoted

items do not behave the same in the string. They take special consideration on

your part when you are building the query. For example: If we wanted a comma

and a space between 2 items, in a standard query, we would use this in Query

Analyzer.

Select LastName + ', ' + SUBSTRING(FirstName, 1, 1) As Emp_Name from Employees

If we want to produce the same thing using a string variable we need to change the single quotes to 2 single quotes per side. The query would look like this.

DECLARE @SQL  VARCHAR(8000)
SET @SQL = 'SELECT LastName + '', '' +
SUBSTRING(FirstName, 1, 1) As Emp_Name from Employees'
EXECUTE(@SQL)

I will leave it to you to experiment with the string building.

We can also control what is returned by the

query through the use of the @RunMode variable. The template procedure is set

up so that if no variables are passed in it defaults to @RunMode = 0 causing

the procedure to return only column headers. This was accomplished through the

following Statement:

SET @RunMode = COALESCE(@RunMode, 0)

It

translates to set @RunMode = return first non-null, else 0

Here is a complete script that uses the Northwind database. ( Insert

USP_Get_Customer_Data Script) Once you compile the procedure, run a test with @Country

= ‘USA’, @Employee = ‘’, and @RunMode = 1. This will have the procedure

generate only the @SQL string. It will look similar to this.

SELECT 
       C.CustomerID AS CUSTOMER_ID,
       C.CompanyName AS COMPANY_NAME, 
       C.ContactName AS CO_CONTACT,
       C.City  AS CITY, 
       C.Country AS COUNTRY, 
       C.Phone AS CUST_PHONE, 
       O.OrderID AS ORDER_NUMBER, 
       SUBSTRING(E.FirstName,1,1) + ' ' + E.LastName    AS ORDER_ENTERED_BY 
FROM 
              Customers     C
JOIN          Orders        O      on C.CustomerID =
O.CustomerID 
JOIN          Employees     E      on O.EmployeeID =
E.EmployeeID 
WHERE C.Country      = 'USA'
ORDER
BY 1
Once we see our result set, we can test it in another window.  If there are any changes needed, merely go back to the procedure; modify it; then re-run the test script.  Just seeing how
your changes affect the @SQL String is a great self teaching tool.. 
By using this slightly longer format for the
procedure, and turning the query into a string, we realize two benefits. One
benefit is that we can control what gets returned from the string by simply
changing the variable @RunMode. The second benefit is that we will not get an
error if no variables are passed to the procedure. Not getting an error is a
great benefit for the development team. Instead of trying to come up with every
way a user can conceivably enter incorrect variable combinations and then
writing error handling scripts or code for them, we can use the default
settings of the procedure to force an empty return. We can then write a single
popup message that gently reminds the user that they need to enter some
variables to get the returns they are looking for.