BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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!
- New Features for Writing SQL in SSMS 2012
- Code Snippets in SQL Server 2012
- 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:
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:
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.
- New Features for Writing SQL in SSMS 2012
- Code Snippets in SQL Server 2012
- Surrounding Code in SQL Server 2012 (this blog)