Shared stored procedure with very complicated input

  • mcraig 42832

    SSC Enthusiast

    Points: 149

    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

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    Passing logic into a stored procedure sounds like a bad idea to me.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • mcraig 42832

    SSC Enthusiast

    Points: 149

    Alvin Ramard - Tuesday, October 17, 2017 7:45 AM

    Passing logic into a stored procedure sounds like a bad idea to me.

    Totally agree...but it's not logic, just data...technically.  But bad idea's aside...the question really is...is there a way to do it?  The alternative is even more ridiculous by having procedure after procedure all doing the exact same thing but with different sets of input.  That's what I'm trying to solve.

  • Sue_H

    SSC Guru

    Points: 90371

    mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

    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

  • mcraig 42832

    SSC Enthusiast

    Points: 149

    Sue_H - Tuesday, October 17, 2017 8:32 AM

    mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

    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.

  • Sue_H

    SSC Guru

    Points: 90371

    mcraig 42832 - Tuesday, October 17, 2017 8:50 AM

    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.

    It sounds like you set in the directions you want to go. I would encourage you to read this article to understand some of the issues and possible alternatives:
    Catch-all queries

    Sue

  • drew.allen

    SSC Guru

    Points: 76686

    It's really hard to say without something even remotely concrete to reference, but I think the first thing you should be looking at is getting rid of the cursors.

    Also, you dismiss using tables as input.  I suspect that you could greatly reduce your 40 parameters if you actually did use tables to store and/or pass some of your "parameters".

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Lynn Pettis

    SSC Guru

    Points: 442205

    The biggest problem, is not seeing what you see.  Anything we give you will just be shots in the dark.  Your explanation does not really help as it is vague as well.  Without knowing what you are currently doing and why, we are in the dark as to how to proceed.

  • Jeff Moden

    SSC Guru

    Points: 995467

    mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

    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).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mcraig 42832

    SSC Enthusiast

    Points: 149

    Jeff Moden - Tuesday, October 17, 2017 8:12 PM

    mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442205

    mcraig 42832 - Wednesday, October 18, 2017 6:22 AM

    Jeff Moden - Tuesday, October 17, 2017 8:12 PM

    mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

    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.

    Why not cursors?  Although CURSORS do have a valid use they are used way too often in situations where they are not the best solution.  They are simply RBAR, a Modenism for Row By Agonizing Row.

    Also, the attitude of "this is a run-once-ish script so I don't care about any kind of overhead" is one that can creep into other code.  Also, if someone else comes across this script and sees that it may help solve another problem may use it not knowing it may be totally inappropriate for what they are trying to accomplish.

    Taking your Looping through a table comment, that demonstrates procedural thinking, not set-based think.  SQL Server works better when coded in a set-based fashion.

    Again, we can't really help you with your current solution because only you can see it.  Any suggestions are just shots in the dark.

  • Jeff Moden

    SSC Guru

    Points: 995467

    mcraig 42832 - Wednesday, October 18, 2017 6:22 AM

    Jeff Moden - Tuesday, October 17, 2017 8:12 PM

    mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

    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.

    Heh... don't forget... you're the one that asked if you were on the totally wrong track and asked for insight.  It's your code.  We're just trying to help. Good luck.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • sgmunson

    SSC Guru

    Points: 110443

    mcraig 42832 - Tuesday, October 17, 2017 8:50 AM

    Sue_H - Tuesday, October 17, 2017 8:32 AM

    mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

    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?".

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • mcraig 42832

    SSC Enthusiast

    Points: 149

    sgmunson - Wednesday, October 18, 2017 2:25 PM

    mcraig 42832 - Tuesday, October 17, 2017 8:50 AM

    Sue_H - Tuesday, October 17, 2017 8:32 AM

    mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

    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?".

    Thanks for your input.

  • x

    SSC-Insane

    Points: 23516

    mcraig 42832 - Tuesday, October 17, 2017 7:09 AM

    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

    I have on at least one occasion had one of those "catch all" queries that would do stuff like this, so I put the particular code in one procedure that would do stuff based on a mix of parameters and then another procedure that would do the same thing each time with the results. A "main" procedure called both. The nice thing is that the "main" procedure that called the other two, before doing any calling did the temp table creation.

    So its like this:

    main procedure:
    - accepted varying list of parameters and creates temp tables, we'll call this "main".
    - call particular stored procedure with mix of parameters (lets call this "particular1"), and this particular stored procedure filled temp tables with work data depending on what parameters were passed
    - call generic stored procedure (lets call this "generic1") that did the same thing each time with results using the previously generated work data

    create procedure test1_variant1
    as
    begin
     insert #t1
     select 1, 'hi from test1_variant1'
    end
    go
    create procedure test1_variant2
    as
    begin
     insert #t1
     select 1, 'hi from test1_variant2'
    end
    go
    create procedure test1_variant3
    as
    begin
     insert #t1
     select 1, 'hi from test1_variant3'
    end
    go

    create procedure test2
    as
    begin
     select * from #t1
    end
    go

    create procedure testmain
    as
    begin
     create table #t1
      (
      testkey int,
      testdata varchar(100)
      );
     exec sp_executesql @stmt = N'exec test1_variant2';
     exec test2;
    end
    go

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply