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
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
7 comments:
Nice one. I am using this one. We can debug in Visual Studio also.
such a simple code & very easy to understand for the beginner
Easy and simple steps. Useful post for me.
Very Good Explanation
Very nice one that i was finding form last several month . But i have a Question how can i view what value in any variable in sp
Super mama
Super,Thanks for posting
Post a Comment