Add support for here-strings i T-SQL
When you work with dynamic SQL you often end up in a maze of nested quotes and plus characters which is difficult to read and maintain. This could be made a lot easier, if T-SQL supported here-string literals like Powershell or Perl. One particular situation where this comes in handy is when you have already a developed a complex SQL statement, which you later need to wrap in dynamic SQL. Presently, you need to review it and double all single quotes. With here-strings, it would be very simple.
I would also like to point out that everything that makes dynamic SQL simpler to work with is good for security, if it can help to reduce the risk for SQL injection.
I suggest that the syntax is drawn from Powershell, but by replacing @ with some other character for instance, $. For instance:
SET @sql = $'
SELECT o.name, COUNT(*)
FROM sys.objects o
JOIN sys.columns c ON o.objectid = c.objectid
GROUP BY o.name
Note: PowerShell supports variable expansion in string literals. While many developers would love this, I absolutely recommend against this, as it would encourage SQL injection.
Would love this after working with here -trings in PowerShell.
L. Szozda commented
I couldn't agree more. This kind of syntax could be very useful. It is supported by PostgreSQL(dollar quoting) and Oracle(text literals):
- all kind of nested SQL strings
- dynamic SQL(just copying static SQL code into dynamic without doubling quotes)
- sp_execute_external_script (R or Python)
- ad-hoc code generation like `SELECT FORMATMESSAGE('SELECT tab_name = ''%s'', cnt = COUNT(*) FROM %s', QUOTENAME(name), QUOTENAME(name))