Let's play with Surrounded With Feature of SQL Server 2012 - Step by Step guidance

Introduction

SQL Server 2012(Denali) has brought a lot of new features .One among them is the Surrounded With Feature.This feature will help us to embed a block of SQL statement within a Begin..End blocks,WHILE loop block or within an IF statement.In this article, we will look into as how we can get the benefit from this feature

Using the code

Case A:

Consider we have the below code segment

	DECLARE @Cnt INT
   
	SELECT @Cnt = Count(mlm.MobileNumber)
	FROM [dbo].[tblMashreqLeadMaster] mlm
	WHERE mlm.MobileNumber = '123456789'

	SELECT
		CAST(
				CASE WHEN @Cnt = 0 THEN 0
						ELSE 1
				END AS BIT
			) AS IsExist;

Our aim is to surround the code block with Begin..End blocks by using the Surrounded With Feature of SQL Server 2012.For this to happen,we need to follow the below steps

Step 1: Select the portion of the code that will come inside the Begin..End blocks

Step 2:Right click and then from the popup, choose Surrounded with or press Ctrlk,Ctrl S

Step 3: Double click on the Begin snippet and the below code will be generated

Case B:

Consider we have the below code segment

		DECLARE @Cnt INT
		DECLARE @IsExist BIT = 1
			    
		SELECT @Cnt = Count(mlm.MobileNumber)
		FROM [dbo].[tblMashreqLeadMaster] mlm
		WHERE mlm.MobileNumber = '123456789'

Our aim is to use the IF blocks by using the Surrounded With Feature of SQL Server 2012 so that depending on the @Cnt value, the @IsExist value will be chosen. For this to happen,we need to follow the below steps

Step 1: Choose the blank area and preform either by right click on the mouse or by pressing Ctrlk,Ctrl S

and choose the IF construct

Step 2:Double click on that and the below appears

So it is clear that we need to put the code block in place properly by setting the condition of the IF statement

Step 3:Put the code block with proper condition

Case C:

Consider we have the below code segment

	DECLARE @counter INT = 0

	PRINT @counter
	SET @counter += 1

Now if we want to turn the above program into a while loop, instead of writing the same by hand, we can take advantage of the Surrounded With.

Step 1:Select the portion that will come inside the While Block.

Step 2:Right click and then from the popup, choose Surrounded with or press Ctrlk,Ctrl S and choose the WHILE LOOP construct

Step 3:Double click on the While snippet and the below code will be generated

We just need to specify the condition and run the program.

References

SQL SERVER 2012 Surrounded With

Conclusion

So in this article we learnt about the usage of Surrounded With Feature of Denali.Hope this was useful.Thanks for reading.