I am writing a rather long SQL script that is running close to 2000 lines of code. Since it becomes really difficult to comprehend a long script, I would like to break it up into logical units and separate them out in other SQL files.
What would be the best way of doing this? Would each logical unit need to be a Stored procedure? maybe a UDF?
Take advantage of stored procedures and functions. If you're doing pure SQL, there's no better place for the logical units of your code. Also, it makes it much easier to maintain/edit your code.
That being said, if your script is that long, you may want to take a look at what you're doing, and think about another scripting language, or even C#/LINQ. Sometimes it makes sense to keep it all in SQL, and sometimes you're forcing something into SQL, but only you can make that determination.
Also, C# or another scripting language easily integrates with source control. Just things to think about!
First, 2000 lines of SQL is not an overly large amount to have in a file (DDL often runs this much or more for a modestly large database). However, it is way too much to have in a single proc! The way to approach this is indeed to break logical components into separate procedures. Unless you have a specific bottleneck to overcome or operations not easily accomplished in SQL, then you won't need UDFs.
One last word of advice: think about simplifying your data access via Views.
BTW: what are you trying to accomplish with such a large proc? I do an enormous amount of work using T-SQL and I've never come close to 2000 lines of code!
If you use UDFs, make sure you know the difference between scalar and inline ones:
What you use to break it up can vary depending on what you are doing.
This is similar to any programming language, ads the scripts/functions get too large, the need to be broken up.
If your script is large due to the same or similar queries, the script could be reduced by adding these queries as views.
Stored procs, and user defined functions are also another good way to go, depending on what your code is doing.
That seems like a lot of code for one batch.
Break out each logical step/process (i.e. create order/dispatch order/register customer) into a separate stored procedure. You may find it useful to create a process flow diagram and to use this to assist with where to break up your T-SQL code batch.
If you are attempting to perform computational logic using T-SQL then move such operations to managed code such as CLR functions etc.
break it up into multiple sps, add try-catch blocks and error handling (remember, division by 0 is not a trapable error), and set up a job to execute each one in sequence.