So, the basics of what I want to accomplish is simple...I have a stored procedure that has some really complex input to work. Currently, the SP just manages by itself but the "code base" is completely re-usable only requiring a set of temporary tables and cursors to loop through them to do the work. The problem is that latter part. There are about 6 temporary table structures and data to go with them and they all get looped through so cursors and variables for their fields for fetching are required. To make the stored procedure "generic", I'd rather not code these things in the stored procedure and passing them all as variables seems...well...improbably. So my idea, like many other languages, is to find a way to "include" or "import" one script into another so I could create all the cursor variables, consumer variables and temporary tables in one script, then execute my stored procedure by passing the script name of the "configuration" settings needed and "poof".
Totally wrong track? Teach me what I don't know? Go back to chewing gum and programming GWBasic?
Thanks for any insight, Mike
Not sure why you're using cursors here. If you could explain those and what the overall goal of the code is, we might be able to help a bit more. I regularly write "generic code" that will, for example, read the first row "header" from files, decide which type of file it is, and import the file to the correct table (indirectly... I load each file into a staging table first and validate the info before inserting it into the final destination table).
Now that's not a bad idea, and one I considered but too is not without its pitfalls. What's wrong with cursors in a stored procedure? I know I'm being asked for more information but don't really feel that's necessary...haven't you all done more with less? All procedural code can follow this basic format: configure your variables / settings & stage what you need, then process the information. I want to be able to make the first part very dynamic and the second part very static. One bit of information that is helpful only in deterring one suggestion is that this is a configuration process. I have a client with 100's of databases, all more or less identical, using a common SaaS code base for web services / ui. There are "common" data elements that each has it's own copy of and, from time to time, "new stuff (data)" has to be installed. The schemas are identical so I can write generic code to populate those targets, but, for this stored proc to be "reusable", I have to alter the "configuration" of what is being installed. So "today", I have 12 items (and their accouterments) that need to be setup in 100 database, tomorrow, I might have 2...configured differently but can be processed identically. So, rather than using a copy of the same script and just changing the configuration portion, I'd like the configuration to by dynamic, introduced to a static processor.
Why cursors? Well why not. Isn't that the most common way to loop through a table of data in a bit of script? The configuration options ARE a temporary table...but if I also have to script the data into a more permanent format, well that just seems a little defeatist...but not beyond the realm of a logical solution. Also, keep in mind, this is a run-once-ish script so I don't care about any kind of overhead.
What I will likely end up doing is writing code outside of SQL to take a simple XML or JSON data structure and dynamically create all the script. That has worked out great for many other solutions, typically a bit shorter and simpler than this but still very useful, particularly when the bosses suddenly change their minds.
Thanks for all the input...it was an interesting exercise for me.