New in SQL 2012 - code snippets and surrounding code
Part three of a three-part series of blogs

In a previous blog on new features of SQL 2012, I missed out the ability to insert code snippets, and also to embed or surround code in an IF, BEGIN or WHILE block. This blog rectifies this!

  1. New Features for Writing SQL in SSMS 2012
  2. Code Snippets in SQL Server 2012
  3. Surrounding Code in SQL Server 2012 (this blog)

Posted by Andy Brown on 15 May 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Surrounding Code in SQL Server 2012

As well as code snippets, SQL Server Management Studio 2012 has introduced a new feature allowing you to embed a block of SQL within a BEGIN, WHILE or IF clause.

Our Example

Suppose that you've written the following SQL:

-- we want to run this statement

-- if today is a Tuesday

SELECT

FilmName,

FilmOscarWins

FROM

tblFilm

You now want to make the comment true (ie, run it only on Tuesdays).  Quite why you would want to do this I'm not sure!

Surrounding Code

A lazy way to solve the above problem would be to embed the SQL code in an IF condition:

Surrounding SQL

Select the block of SQL you want to surround, then right-click and choose this option.

 

You can now choose to embed this SQL in one of 3 blocks:

Choosing to surround with IF

Double-click on the construct you want to use (here it'll be IF).

 

SSMS 2012 will encase the SQL selected in a condition:

-- we want to run this statement

-- if today is a Tuesday

IF ( Condition )

BEGIN

SELECT

FilmName,

FilmOscarWins

FROM

tblFilm

END

Finishing our Example

You can now amend the condition to what you wanted to say:

-- we want to run this statement

-- if today is a Tuesday

IF( DateName(weekday,GetDate()) = 'Tuesday' )

BEGIN

SELECT

FilmName,

FilmOscarWins

FROM

tblFilm

END

It won't set the world alight, but it might save you a bit of typing.  You can press CTRL + K followed immediately by CTRL + S to surround code without using your mouse.

  1. New Features for Writing SQL in SSMS 2012
  2. Code Snippets in SQL Server 2012
  3. Surrounding Code in SQL Server 2012 (this blog)
This blog has 0 threads Add post