How to write and Debug stored procedure which are having more than 1000 lines code ?
Hi All, In my recent project I have observed that most of the stored procedures are having more than 1000 lines. Till now I did n`t write this type of stored procedures or T-sql. Please tell me some tips to write and debug this type of procedures to reduce my nervous. Is there any tool to check the code by hiding the IF and ELSE ladders. for example if I want to check the ELSE part code directly goes to ELSE part by one click IF condition. (like _ and + symbols to hide and expand the code) Thank you All in Advance.........
That's pretty abnormal to see a majority of the code in huge 1000+ lines all over the place. A few of those is, unfortunately, common. Lots of code like that is an issue. In general, you're going to have a tough time of it. SQL Server 2008+ does have the ability to collapse parts of the code within SSMS so you can group things together. But, you have to have SQL Server Intellisense turned on to make that work. You can also create and save bookmarks to make it easier to move between sections of code. Other than that, I'm not aware of any methods that help with large volumes of code like this. You might want to take a look at Red Gate's SQL Prompt (
http://www.red-gate.com/products/sql-development/sql-prompt/). It can help you with formatting the code and has much better code completion than Microsoft's Intellisense. Disclaimer, I work for Red Gate.