THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Tidy PowerShell Scripts using Here-strings

If you use PowerShell in combination with SQL Server, you no doubt have had to compose a string full of T-SQL. If you compose a query string with a lot of quotation marks and concatenation operators, it can be hard to read and even harder to edit. Here's a tip I discovered a while back that makes it much simpler. Check out the "here-string:"

Instead of this construction:

$NoisyQuery1 = 'SELECT ' 
$NoisyQuery1 = $NoisyQuery1 + "columna, " 
$NoisyQuery1 = $NoisyQuery1 + "columnb, " 
$NoisyQuery1 = $NoisyQuery1 + "columnc  " 
$NoisyQuery1 = $NoisyQuery1 + "FROM dbo.someTable " 
$NoisyQuery1 = $NoisyQuery1 + "WHERE foo = foo " 
$NoisyQuery1 = $NoisyQuery1 + "AND bar = bar " 
$NoisyQuery1 = $NoisyQuery1 + " etc. etc.; "

$NoisyQuery1

or this:

$NoisyQuery2 = 'SELECT ' `
  + "columna, " `
  + "columnb, " `
  + "columnc  " `
  + "FROM dbo.someTable " `
  + "WHERE foo = foo " `
  + "AND bar = bar " `
  + " etc. etc.; "

$NoisyQuery2

Try this:

$CleanerQuery = @"
  SELECT
   columna,
   columnb,
   columnc
  FROM dbo.someTable
  WHERE foo = foo
   AND bar = bar
    etc. etc.
"@

$CleanerQuery 

Better, no?

See:

http://www.johndcook.com/PowerShellCookbook.html

http://technet.microsoft.com/en-us/library/ee692792.aspx

Published Tuesday, October 19, 2010 3:30 PM by merrillaldrich
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement