October 17, 2017 at 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
October 17, 2017 at 7:45 am
Passing logic into a stored procedure sounds like a bad idea to me.
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]
October 17, 2017 at 8:09 am
Alvin Ramard - Tuesday, October 17, 2017 7:45 AMPassing 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.
October 17, 2017 at 8:32 am
mcraig 42832 - Tuesday, October 17, 2017 7:09 AMSo, 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
October 17, 2017 at 8:50 am
Sue_H - Tuesday, October 17, 2017 8:32 AMmcraig 42832 - Tuesday, October 17, 2017 7:09 AMSo, 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.
October 17, 2017 at 10:31 am
mcraig 42832 - Tuesday, October 17, 2017 8:50 AMWell, 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
October 17, 2017 at 12:10 pm
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
October 17, 2017 at 4:22 pm
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.
October 17, 2017 at 8:12 pm
mcraig 42832 - Tuesday, October 17, 2017 7:09 AMSo, 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
Change is inevitable... Change for the better is not.
October 18, 2017 at 6:22 am
Jeff Moden - Tuesday, October 17, 2017 8:12 PMmcraig 42832 - Tuesday, October 17, 2017 7:09 AMSo, 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.
October 18, 2017 at 9:11 am
mcraig 42832 - Wednesday, October 18, 2017 6:22 AMJeff Moden - Tuesday, October 17, 2017 8:12 PMmcraig 42832 - Tuesday, October 17, 2017 7:09 AMSo, 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.
October 18, 2017 at 11:13 am
mcraig 42832 - Wednesday, October 18, 2017 6:22 AMJeff Moden - Tuesday, October 17, 2017 8:12 PMmcraig 42832 - Tuesday, October 17, 2017 7:09 AMSo, 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
Change is inevitable... Change for the better is not.
October 18, 2017 at 2:25 pm
mcraig 42832 - Tuesday, October 17, 2017 8:50 AMSue_H - Tuesday, October 17, 2017 8:32 AMmcraig 42832 - Tuesday, October 17, 2017 7:09 AMSo, 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 19, 2017 at 6:02 am
sgmunson - Wednesday, October 18, 2017 2:25 PMmcraig 42832 - Tuesday, October 17, 2017 8:50 AMSue_H - Tuesday, October 17, 2017 8:32 AMmcraig 42832 - Tuesday, October 17, 2017 7:09 AMSo, 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.
October 20, 2017 at 1:38 pm
mcraig 42832 - Tuesday, October 17, 2017 7:09 AMSo, 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 25 total)
You must be logged in to reply to this topic. Login to reply