Storing TSQL Queries in a Table Without Losing Formatting

By June 21, 2011Tips

Guest post by Jacob Sebastian of Beyond Relational

One of the applications we worked on recently had to deal with storing TSQL queries in a table and executing them based on some business logic. It was not a very complicated project, but the development team had a tough time with the formatting of the queries stored in the table. This was a huge problem when the queries were to be modified.

The Problem

The developer writes a well formatted TSQL code and stores it into the table. Later on when a change request comes, he retrieves the query text from the table and finds that all the formatting is lost.

Why Does It Happen?

Well, the formatting is not really lost. When the developer stores a well formatted query into the VARCHAR(MAX)/NVARCHAR(MAX) column of a table, the formatting is also stored. The problem is the way he retrieves it.

The common way of retrieving the query text is to run a SELECT query which will display the result in a grid view. The grid view does not maintain the special characters (line feed, carriage return). That is the reason why you are losing the formatting.

An immediate workaround is to change the output to text and run the SELECT query. This works if the TSQL queries stored in the table are small. If the queries are large, then it might truncate the queries.

Workaround

In this post, let us see a workaround that shows how to retrieve the query text without losing the formatting. To see this in action, let us start by creating a table to store the queries.

[sql]
USE tempdb
GO

IF OBJECT_ID('BRQueries','U') IS NOT NULL BEGIN
	DROP TABLE BRQueries
END

CREATE TABLE BRQueries(
	QueryID INT,
	QueryText VARCHAR(MAX)
)
[/sql]

Next, let us write a well formed query.

[sql]
SELECT
   t.Name AS TableName,
   c.Name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON
	t.object_id = c.object_id
[/sql]

Next, let us run a SELECT query to retrieve the query text. If we use ‘result to grid’, we will loose the formatting. If we use ‘result to text’ we will get the correctly formatted text in this case. However, if the query is longer it might get truncated. If the table has several other columns and rows, the output will be completely messed up.

A workaround is to use to use the XML function processing-instruction(). Run the following query.

[sql]
SELECT
	QueryID,
	(
		SELECT QueryText AS 'processing-instruction(q)'
		FROM BRQueries b
		WHERE b.QueryID = a.QueryID
		FOR XML PATH(''), TYPE
	) AS Query
FROM BRQueries a
[/sql]

Take the result of the query into a grid either by pressing CTRL+D or from the toolbar and you will see the result as follows:

table results of a SQL query

Click on the desired row and it will open up the query text with the original formatting.

[sql]
<!--?q SELECT    t.Name AS TableName,    c.Name 
AS ColumnName 
FROM sys.tables t INNER JOIN sys.columns c 
ON 	t.object_id = c.object_id ?-->
[/sql]

Ready for your ad-hoc reporting problems to go away? Get a free trial of Izenda to see it in action with your data.