If you have a complex SQL storeProcedure that you want to troubleshoot, the chances are you want to find out what values are being passed to the storeprocedure in the first. In a running application, it is hard to find out. On easy way is this which will solve a lot of your problems and possibly headache.
create procedure usp_GetCustomer
param @customer_id int
select customer_name, customer_address from customer_tbl where customer_id = @customer_id
Now lets say you are using this storeprocedure in your gridview and you are getting no result, i.e, the gridview does not populate. An easy way to see what is going on is modify the query to this
select @customer_id as customer_name, customer_address from customer_tbl where customer_id = @customer_id
Now the first field name “customer_name” will spit out the parameter that was passed to the stored procedure. This way you will know exactly what is going wrong in your stored procedure. I hope it helps.