How to debug stored procedures in SSMS (Sql server management studio)

How to debug stored procedures in SSMS (Sql server management studio)

To debug a stored procedures or functions in SSMS (sql server management studio 2008 r2) 

Step 1: Create stored procedure which you want to debug. Suppose I have created following stored procedure:

CREATE PROC TestDebug(

@MaxLimit AS INTEGER

)

AS

DECLARE @Count AS INTEGER

SET @Count = 0

WHILE @Count < @MaxLimit BEGIN

SELECT @Count 

SET @Count = @Count + 2

END

Step 2: To debug this stored procedure, write an execute procedure statement and add breakpoints. As shown in the following figure:

Step 3: Click on debug button or press Alt + F5. Debug button has shown in following screen by read oval.

Step 4: To go next lines press F11 key or click on step into button in SSMS. Control will move to code of stored procedure as shown in the following figure:

Step 5: Repeat the step 4 to go to the next line. Also at bottom in the Locals window you can get values and data type of the local variable.

Note: In debugging mode we can edit the value of variables.  

What is non clustered index in sql server
When sql server is able to use indexes and when not
How to use database engine tuning advisor to get recommendations in sql server
Effect of collation in indexing in sql server.
Write sql queries in set based approach sql server
Sql server best practices for stored procedure
What is Adhoc query in sql server: Cached Query Plan
Subquery vs inner join which one is better in sql server
Difference between actual execution plan and estimated execution plan in sql server