Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get a Return Every Time.

By Charles Eaton,

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:

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


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.

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

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.

       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 
              Customers     C
JOIN          Orders        O      on C.CustomerID =
JOIN          Employees     E      on O.EmployeeID =
WHERE C.Country      = 'USA'
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.


USP_Get_Customer_Data_Northwind_Example.sql | USP_TEMPLATE.sql
Total article views: 5150 | Views in the last 30 days: 2
Related Articles

Dynamic SQL issues with passing variables in stored procedure

stored procedure can't handle variable in sql string


String Operation in Stored Procedure

String Operation in Stored Procedure


Using a string as a table variable

Using a string as a table variable


Search for a String in all Stored Procedures and beyond?

Search for String Occurence with this script in your stored procedures.


Convert a string list to TABLE with single CTE query



Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place


Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones