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 loosing 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 loosing the formatting. To see this in action, let us start by creating a table to store the queries.

USE tempdb
GO

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

CREATE TABLE BRQueries(
	QueryID INT, 
	QueryText VARCHAR(MAX)
)

Next, let us write a well formed query.

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

Let us now insert this query into the table

INSERT INTO BRQueries (QueryID, QueryText)
SELECT 1, 
'
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'

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.

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

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:

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

<?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
?>

You can strip of the XML tags from the top and bottom of the query text and go ahead with editing. Well, this is not a 'great' method but a quick and dirty trick that might come handy at times.