Step Into/Debug SQL Stored Procedure in Visual Studio.Net

.Net developers from time to time have to write their own stored procedures when doing data access. The tools that we use are basically (1)Text editors, such as the notepad, (2) the Query Analyzer, (3) Sql Profiler. Few people know that Visual Studio .Net now has the capability to step into a running stored procedure, giving developers the ability to check on values while the stored procedures are running, instead of using the "Print" or "Select" statement inside the query analyzer. We will illustrate this amazing tool by an somewhat complex example. We will use the Northwind sample database. Let's suppose we want to get the employees who have handled the biggest customer by sales amount. Here is the stored procedure we use, and we name it "whoHandledBigCustomer".declare @customerId as varchar(40) --will hold the customerId of biggest customerdeclare @MaxAmount as float --will hold the total amount of that customer's orders --get the amountselect @MaxAmount = max(T1.Total) from(select sum(unitPrice*(1-discount)*quantity) as Total, orders.customerId from ordersinner join [Order Details]on orders.orderId =[Order Details].orderIdgroup by orders.customerID ) as T1--get the biggest customer idselect @customerId=T.customerId from(select sum(unitPrice*(1-discount)*quantity) as Total, orders.customerId from ordersinner join [Order Details]on orders.orderId =[Order Details].orderIdgroup by orders.customerID ) as Twhere T.Total >= @MaxAmountselect @customerId, @MaxAmount--get the employees who handles that customer, firstname, lastname, and phoneselect firstname, lastname, HomePhone from employeeswhere employeeId in (select employeeId from orderswhere customerId=@customerId)Once you have this stored procedure, you can locate it in the Visual Studio.Net's server explorer, by drilling down the hierachy. Once you find our "whoHandledBigCustomer" stored procedure in the panel, right click, you will see the context menu shown up like this:

Assume you have the right permissions, if you "Run Stored Procedure", you will get the result sets in the "Output window". The cool feature we want is to "Step Into Stored Procedure". So we go into that by clicking on that option. Then we see in the main surface something like this:

If you mouse over the variables we declared in the stored procedure, you can see in the tool tip what value they have right now. "@customerId" now is null, because no statement has been executed. Note that you cannot step into the select statement itself, because visual studio uses the Sql debugger which hooks up into the atomic parts of stored procedure. One select statement is considered an "atom", which can't be subdivided further. The next version of Visual Studio .Net might have the functionality to step into stored procedures seamlessly from your .Net language code. So you can save a lot of right-clicks. Note that you might need to configure permissions for the visual studio developer account to run sql debugger, if it is not configure correctly, the operating system will reject your "step into" attempt. Just another gotcha from the security perspective.