Click here to Skip to main content
14,099,174 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I am running one existing complex store procedure and in some cases i couldnot find wrong results .I want to debug the procedure.But as data is large i could not debug store procedure.I used print statements as well.

What I have tried:

For analysing
1. i used PRINT STATEMENT
2. I used some Temp tables as well
3. I tried debugging but it fails as large data and hangs stored procedure .
4. Also I tried in some extent SQL Server Profiler but even then I Couldn't find error.
Will anyone tell me best way with handling performance and perfect way to debug Stored Procedure
Posted
Updated 18-Sep-18 2:49am
Comments
CHill60 18-Sep-18 7:59am
   
You can't really follow progress in a stored procedure using PRINT - try using RAISEERROR with NOWAIT instead - Using the NOWAIT option with the SQL Server RAISERROR statement[^]
Another technique is to pare the stored procedure right down (even if right down means it does nothing), then add functionality back in until it fails
suresh shewale 18-Sep-18 8:21am
   
thanks for reply...As per your suggestion i will check first option NOWait .But I didnt understand your second option ...Can you please elaborate it ?....
CHill60 18-Sep-18 9:40am
   
It's hard to explain without a specific example but imagine your store procedure had 3 blocks of sql statements
SQL block 1
SQL block 2
SQL block 3
You need to focus on each section to find where the error is so you would comment out everything except the first block and try to run your code e.g.
SQL block 1
/* SQL Block 2 */
/* SQL Block 3 */
If you get the error then you know the problem is in Block 1 but if you don't get any errors then you know the problem is in Block 2 OR 3, so only uncomment the next bit
SQL block 1
SQL Block 2
/* SQL Block 3 */
And so on.
Does that help?
suresh shewale 19-Sep-18 1:26am
   
sorry for Delay ...Thanks for your suggestion .I am working now on it and I am sure that I will get resolve issue ....Thanks once again to you and other digimanus,Lopez also .....
ZurdoDev 18-Sep-18 7:59am
   
Sounds like you're doing the proper things.
suresh shewale 18-Sep-18 8:22am
   
thanks...any suggestion ?
ZurdoDev 18-Sep-18 8:50am
   
Do as suggested in the previous comment. Cut out stuff until you find what is wrong. Then fix that.
digimanus 18-Sep-18 8:51am
   
You can use TRY CATCH in SQL Server. In the BEGIN CATCH you can find out where the SP went wrong.
Matias Lopez 18-Sep-18 9:39am
   
Use Profiling Services for SQL, it is included in the SQL Packing.
CHill60 18-Sep-18 9:41am
   
OP says they have already tried the SQL Profiler
Matias Lopez 18-Sep-18 9:45am
   
Yes, but it's the best option, then run the SP exec statement (with the profiling parameters), and the run the SP manual with variables, and try to catch the error.
Gerry Schmitz 19-Sep-18 12:36pm
   
There is no such thing as a "complex SQL query"; only badly written ones.

Since you show no SQL, you must be too embarrassed.
itsmypassion 26-Oct-18 5:48am
   
You can debug stored procedure.
https://msdn.microsoft.com/en-us/library/hh272701(v=vs.103).aspx

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190518.1 | Last Updated 18 Sep 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100