Home of sqtpp - A Preprocessor for SQL

You are a professional SQL developer? You are writing of SQL scripts that have to be maintained for a long while? Then sqtpp is a tool you might consider using for your daily work.

sqtpp is one of those little tools that offer you some features you don't recognise at the first moment. But when you got used to it you wont belief that you can live without its features. So, what does it do? Maybe a litte insight into the motivation for development may help.

Intro / History

Back in the 90' we developed client server applications for a german broadcast company. While the first customer used Sybase as theire backend server some other customers where using the same client but preferred MS SQL Server as their backend solution. Both database servers were using transact SQL for server side programming or scripting. We kept the source code for the backend in the same files allowing us to install the databases just by exchaning the SQL command processor. However although Sybase and MS Sql Server have the same ancestry there are some little differences in the SQL dialect. e.g. to determine the length of a character attribute or value Sybase defines the function char_length while you could use datalength for MS SQL Server. To be able to use the same source code for both plattforms we use the standard c preprocessor to define some macros that produce the correct dialect. Before the scripts were executed they were processed by the C pre processor and then its output was send to the specific target server.

After the pre processing had been established it was only a small step to use it for other purposes as well:

Reduction of code by using macros where stored procedures are not appropriate

If you script your objects you normally generate code that drops the object before you (re-)create it. The code looks always the same e.g. to create a procedure you get the following code:

    1 if exists ( select 1 from sys.objects where object_id = object_id( 'dbo.foo' and type = 'P')

    2     drop procedure dbo.foo

    3 go

Using a macro you can simplify this to

    1 CSQL_DROP_PROCEDURE( dbo.foo )

    2 go

Using the appropriate macro definition. For an example refer to the sample macro package.

Usage of symbols instead of numerical values

The advantage of using symbols instead of numerical values are rather easys to understand. It make the source better readable and maintable. Imagine a stored procedure which raises an error like in the following example:

    1 create procedure foo( @param int )

    2 as

    3 begin

    4    if @param is null

    5    begin

    6       raiserror 51000 '@param'

    7       return

    8    end

    9 end

   10 go

Compare it with the following code:

    1 create procedure foo( @param int )

    2 as

    3 begin

    4    if @param is null

    5    begin

    6       raiserror SQLERR_ARGUMENT_NULL '@param'

    7       return

    8    end

    9 end

   10 go

Despite the fact that the error is better readable you are able to use the same symbol in other scripts e.g. a insert script containing all error you use:

    1 insert into ErrorCodes ( ErrorCodeId, ErrorText )

    2 values ( SQLERR_ARGUMENT_NULL, 'Invalid null value in procedure arguments.' )

Last not least you are able to use the same symbol in your client code to handle different kinds of errors:

Today

Over the years we came to point where standard c pre processor had to many limitations. While it is hard to imaging to write scripts without the advantages we needed features it couldn't provide:

  • Support of unicode source to be able to write scripts for international customers.
  • More buildin/predefined macros e.g. to provide the possibility to quote user defined symbols.
  • Better support for the SQL syntax e.g. usage of single quote for string instead of the C default double quotes.
  • Expansion of macros into multiple lines for better readbility of the pre processor output.
  • etc…

As a result we developed our own pre processor we are providing here. We use it in conjuction with bsql a simple sql batch processor that understands the optional #pragam line # output of sqtpp. The tools are integrated into Visual Studio as external tools emitting their ouput into the result pane. See the example project for further descriptions.

The macro packages we are using today are quite elaborated. They do not only make the scripts compatible between diffent SQL Server products but also give you some functionality we are missing in the native SQL syntax.

Future

Like every other software else sqtpp can be made better. Our next goal is better integration into Visual Studio. However sqtpp in conjuction with bsql works pretty well at the moment so that may take a while.