Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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.object_id = c.object_id
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.

3 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Erland Sommarskog shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base