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)
)
Read more: Beyond Relational
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)
)
Read more: Beyond Relational