DEV Community

Zohar Peled
Zohar Peled

Posted on • Edited on

5

A quick tip for dynamic SQL

Anyone that's ever worked with dynamic SQL before knows it can get really messy and hard to read and maintain - which is why before actually executing the dynamic SQL, it's best to first just print it just so you could check it visually to make sure everything looks alright before executing it - Here's where this tip comes in handy: Add couple of variables to your script - one for line breaks and one for tabs, and use them when building your dynamic SQL - so when you finally print it, it's going to be nicely formatted and easy to read.

This code example is in T-SQL, but of course this tip can be used in any rdbms:


DECLARE @Sql nvarchar(max), 
    @Tab nchar(1) = NCHAR(9),
    @LineBreak nchar(2) = NCHAR(13) + NCHAR(10),
    @ColumnSeperaor nchar(5);

SET @ColumnSeperaor = N',' + @LineBreak + @Tab + @Tab;

SELECT @Sql = N'SELECT  '+ STRING_AGG(QUOTENAME(@tableName) + N'.'+ QUOTENAME(Column_name), @ColumnSeperaor) + @LineBreak
FROM Information_Schema.Columns
WHERE Table_Name = @TableName

SELECT @Sql = @Sql + N'FROM '+ QUOTENAME(@TableName)

PRINT @SQL 
Enter fullscreen mode Exit fullscreen mode

This will result with a nicely formatted, readable SQL such as:

SELECT [TableName].[Col1], 
       [TableName].[Col2], 
       [TableName].[Col3] 
FROM [TableName]
Enter fullscreen mode Exit fullscreen mode

This was also edited into my blog post entitled The do’s and don’ts of dynamic SQL for SQL Server

Image of Datadog

How to Diagram Your Cloud Architecture

Cloud architecture diagrams provide critical visibility into the resources in your environment and how they’re connected. In our latest eBook, AWS Solution Architects Jason Mimick and James Wenzel walk through best practices on how to build effective and professional diagrams.

Download the Free eBook

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more