November 14, 2012 at 10:03 am
Hi all,
I have the pleasure of inheriting a select statement that has multiple subqueries and unions in subqueries, and all sorts of logic to the tune of 400 lines. It is throwing a conversion error and I'm having a hard time pinpointing where it is coming from. How would you guys debug a single, huge select statement like this? I can't put breakpoints in various parts since it's just one select statement. There are no variables involved, so the Locals window is useless as well.
Also, I am not a developer or programmer, so I have limited exposure to the Debug functions in SQL Server. Which is why I need help from those that are pros at this. Thanks in advance!
November 14, 2012 at 12:08 pm
shahgols (11/14/2012)
Hi all,I have the pleasure of inheriting a select statement that has multiple subqueries and unions in subqueries, and all sorts of logic to the tune of 400 lines. It is throwing a conversion error and I'm having a hard time pinpointing where it is coming from. How would you guys debug a single, huge select statement like this? I can't put breakpoints in various parts since it's just one select statement. There are no variables involved, so the Locals window is useless as well.
Also, I am not a developer or programmer, so I have limited exposure to the Debug functions in SQL Server. Which is why I need help from those that are pros at this. Thanks in advance!
Start by figuring out what type of conversion error you are getting. The datatype mentioned in the error will give you a starting point. Then remove anything in the where clause. If the query runs at that point you at least know the issue is in the where clause. Look at the datatypes of the columns being joined and identify anywhere the underlying columns do not have matching datatypes. With the complete lack of details that is about the best advice I can offer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2012 at 5:16 pm
shahgols (11/14/2012)
Hi all,I have the pleasure of inheriting a select statement that has multiple subqueries and unions in subqueries, and all sorts of logic to the tune of 400 lines. It is throwing a conversion error and I'm having a hard time pinpointing where it is coming from. How would you guys debug a single, huge select statement like this? I can't put breakpoints in various parts since it's just one select statement. There are no variables involved, so the Locals window is useless as well.
Also, I am not a developer or programmer, so I have limited exposure to the Debug functions in SQL Server. Which is why I need help from those that are pros at this. Thanks in advance!
WHAT is the actual error message? Also, was the code ever in service before?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2012 at 5:38 pm
I often need to do the same task as you, working with someone else's SQL statement, usually to create something similar. I like to copy the original SQL statement into a New Query window and make updates to the copy. Since in this case there are many unions and sub-queries, I would split each section of the statement at the union or sub-query clause and verify that each part worked correctly on its own. I sometimes include PRINT statements between the parts. If one of the statements fails, at least some of the PRINT statements and SELECT statements would show their output before the failure. Then I could narrow my checking to the first statements below the working parts.
November 14, 2012 at 8:42 pm
I often find inherited SQL to be poorly formatted. Just getting things to line up helps me wrap my mind around what's going on.
My first step is to copy & paste the code into http://poorsql.com and take the formatted SQL into a new window. You can set the formatting options to match your standards/preferences.
Rob
November 14, 2012 at 11:54 pm
Since, its a conversion error.....I would look at the error to know the datatypes in the error message. Then I would look at comparisons(equal to, greater than, lesser than etc.) in the Query - comparisons between two columns of tables, comparisons between a column and a temporary variable, comparison between two temporary variables etc.
Since it is a 400 line code....I would always keep in mind the datatypes shown in the error message so that I am only debugging that part of the query which uses the datatypes shown in the error message.
November 15, 2012 at 4:13 am
Whilst we are on the subject, I am not a big fan of sub-selects and co-related sub queries, even though the logic that is implemented is vital in many query situations
If you are using SQL2005 or above, you can (almost always) rewrite these as Common Table Expresssions which has the benefit that each can be tested independantly and keeps the main query clean as the CTEs are simply referenced like normal permanent or temp tables and unsually there is no change in the query plan.
November 15, 2012 at 12:08 pm
Many thanks to everyone for their informative suggestions, I learned a few things that I can always put to use!
November 16, 2012 at 2:55 am
Thanks for the http://poorsql.com tip, didn't know this existed and it works like a charm.
Vera
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply