What is best option to analyse complex stored procedure in SQL server

Question

0.00/5 (No votes)

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 18-Sep-18 0:32am

suresh shewale

Updated 18-Sep-18 2:49am

Add a Solution

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.

Herman<T>.Instance 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.

[no name] 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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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