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
My vote would be for Totally wrong track multiplied by 1000.
SQL is not like programming languages you have described. Generic stored procedures filled with dynamic, temporary pieces to allow for different logic to be used will result in unstable plans. And to have one with 6 temporary tables built for loops so cursors and variables gets populated sounds like something like nothing short of a nightmare.
Hard to say what to teach - what is your experience with databases, SQL and stored procedures?
Sue
Well, if there is no way through TSQL to do it, then that's my answer. Perhaps a different explanation. Imagine you have a stored procedure that needs 40 variables passed to it. Sound ludicrous right? So wouldn't it be nice if you could "pull" that configuration from some other source (not a database)? The code I have now works absolutely wonderfully and temp tables and looping through cursors to build out data is EXACTLY what stored procedures are useful for...but for every way you can do one thing, there are three others...and I can do those too. The point was to find out if there was a path I could take in THIS direction. If there's not, that's fine too. And pretty old hat with all sorts of databases...just looking for a different path.
Honestly, the idea that you can't pass a set of parameters via a table is ludicrous. It's done all the time. Heck, you could even use the same table for a large number of stored procedures, and insert the parameter data into the table with some kind of unique identifying value (not a GUID, please), and only pass that unique value to the SPROC and then it can read the table records with that unique value and then go to work. You DO need to review those cursors for replacement with set-based T-SQL code, as unless you're doing some kind of conditional update to one record based on what happened in some other record update to the same table, cursors are generally just not necessary, and it's just that no one could think of how to do it. This forum is pretty darned excellent at helping folks solve that kind of problem. Please keep an open mind and keep learning about set-based methodology - it's one that says "What do you want to do to a Column?" as opposed to "What do you want to do to a Row?".