Post reply

Shared stored procedure with very complicated input

  • October 20, 2017 at 2:24 pm

    #1964589

    patrickmcginnis59 10839 - Friday, October 20, 2017 1:38 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

    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

    ah HA!  Exactly what I'm looking for...thanks for that !

  • October 20, 2017 at 4:50 pm

    #1964623

    mcraig 42832 - Friday, October 20, 2017 2:24 PM

    patrickmcginnis59 10839 - Friday, October 20, 2017 1:38 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

    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

    ah HA!  Exactly what I'm looking for...thanks for that !

    Forgot to include the obligatory link about catch all queries in case you're using them in your dynamic sql!

    https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  • November 9, 2017 at 1:18 pm

    #1967453

    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.

    As to your "Why cursors?  Well why not." comment, there's a really simple answer:  PERFORMANCE.   Cursors just can't perform very well as they are what we call RBAR (row by agonizing row).   A properly designed set-based solution generally smokes a cursor on performance.   So therefore, I'd say "Cursors?  You must be kidding!"

    As to your statement on "it was an interesting exercise for me", I've got a rather different perspective.   Your tone from the very beginning was making it clear that you really aren't very interested in any idea that doesn't somehow strike your fancy or comport with your procedural world-view.   If you're going to ask for help from volunteers, at least take the time to understand why cursors are a bad idea instead of just dismissing any nay-sayers.   After all, if you're not going to take anyone else's expert advice, why ask in the first place?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • November 9, 2017 at 3:43 pm

    #1967472

    As to your "Why cursors? Well why not." comment, there's a really simple answer: PERFORMANCE. Cursors just can't perform very well as they are what we call RBAR (row by agonizing row). A properly designed set-based solution generally smokes a cursor on performance. So therefore, I'd say "Cursors? You must be kidding!"

    Reading the OP's post it looks like his main concern wasn't performance but probably more like complexity with the addition of having to perform repetitive operations on a list of database targets. I'm not beating up on performance, but that doesn't look to be in his list of concerns.

    I've had to bail to loops before. I have not found a set oriented method to versioning database installations for instance because each test needs a new connection and you cannot (easily?) do set oriented connections, and even if I could, it wouldn't seem to be of any profit unless I had millions of database servers and then I'd spend the effort to marshall some other programming resources to do some threading or something . If you read his post in depth, you would have noticed he's describing looping over databases and performing operations on each one. He WANTED to loop. Warn him about cursor performance once maybe, sure why not, but the extent that happened in this thread just seems so disagreeable.

    As to your statement on "it was an interesting exercise for me", I've got a rather different perspective. Your tone from the very beginning was making it clear that you really aren't very interested in any idea that doesn't somehow strike your fancy or comport with your procedural world-view. If you're going to ask for help from volunteers, at least take the time to understand why cursors are a bad idea instead of just dismissing any nay-sayers. After all, if you're not going to take anyone else's expert advice, why ask in the first place?

    He wasn't negative at my post. Its probably because I didn't berate him over something he said that really wasn't all that bad.

    The guy really had a valid question about programming using T-SQL that didn't involve set versus procedural honestly. Theres no question that set based coding has advantages, and all he has to do is google something like "slow cursor sql" and he doesn't have to post a question anywhere.

    Speaking of tone, don't you think yours was a bit sour?

  • November 14, 2017 at 7:06 am

    #1967954

    patrickmcginnis59 10839 - Thursday, November 9, 2017 3:43 PM

    As to your "Why cursors? Well why not." comment, there's a really simple answer: PERFORMANCE. Cursors just can't perform very well as they are what we call RBAR (row by agonizing row). A properly designed set-based solution generally smokes a cursor on performance. So therefore, I'd say "Cursors? You must be kidding!"

    Reading the OP's post it looks like his main concern wasn't performance but probably more like complexity with the addition of having to perform repetitive operations on a list of database targets. I'm not beating up on performance, but that doesn't look to be in his list of concerns.

    I've had to bail to loops before. I have not found a set oriented method to versioning database installations for instance because each test needs a new connection and you cannot (easily?) do set oriented connections, and even if I could, it wouldn't seem to be of any profit unless I had millions of database servers and then I'd spend the effort to marshall some other programming resources to do some threading or something . If you read his post in depth, you would have noticed he's describing looping over databases and performing operations on each one. He WANTED to loop. Warn him about cursor performance once maybe, sure why not, but the extent that happened in this thread just seems so disagreeable.

    As to your statement on "it was an interesting exercise for me", I've got a rather different perspective. Your tone from the very beginning was making it clear that you really aren't very interested in any idea that doesn't somehow strike your fancy or comport with your procedural world-view. If you're going to ask for help from volunteers, at least take the time to understand why cursors are a bad idea instead of just dismissing any nay-sayers. After all, if you're not going to take anyone else's expert advice, why ask in the first place?

    He wasn't negative at my post. Its probably because I didn't berate him over something he said that really wasn't all that bad.

    The guy really had a valid question about programming using T-SQL that didn't involve set versus procedural honestly. Theres no question that set based coding has advantages, and all he has to do is google something like "slow cursor sql" and he doesn't have to post a question anywhere.

    Speaking of tone, don't you think yours was a bit sour?

    And at what point did the original poster actually take anyone's advice?   And yet you see no problem with their tone?   Maybe you are willing to just ignore those facts, but I cannot and will not.  It was quite clear from their comments that any solution that was anything other than exactly what they wanted to hear just wasn't worth their time.   Sorry, but I'm not willing to try and help someone who's looking for validation of a not so great idea and does nothing but ignore everyone's advice.   Call it sour if you want to...   I just call 'em as I see 'em.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • November 14, 2017 at 8:11 am

    #1967970

    sgmunson - Tuesday, November 14, 2017 7:06 AM

    patrickmcginnis59 10839 - Thursday, November 9, 2017 3:43 PM

    As to your "Why cursors? Well why not." comment, there's a really simple answer: PERFORMANCE. Cursors just can't perform very well as they are what we call RBAR (row by agonizing row). A properly designed set-based solution generally smokes a cursor on performance. So therefore, I'd say "Cursors? You must be kidding!"

    Reading the OP's post it looks like his main concern wasn't performance but probably more like complexity with the addition of having to perform repetitive operations on a list of database targets. I'm not beating up on performance, but that doesn't look to be in his list of concerns.

    I've had to bail to loops before. I have not found a set oriented method to versioning database installations for instance because each test needs a new connection and you cannot (easily?) do set oriented connections, and even if I could, it wouldn't seem to be of any profit unless I had millions of database servers and then I'd spend the effort to marshall some other programming resources to do some threading or something . If you read his post in depth, you would have noticed he's describing looping over databases and performing operations on each one. He WANTED to loop. Warn him about cursor performance once maybe, sure why not, but the extent that happened in this thread just seems so disagreeable.

    As to your statement on "it was an interesting exercise for me", I've got a rather different perspective. Your tone from the very beginning was making it clear that you really aren't very interested in any idea that doesn't somehow strike your fancy or comport with your procedural world-view. If you're going to ask for help from volunteers, at least take the time to understand why cursors are a bad idea instead of just dismissing any nay-sayers. After all, if you're not going to take anyone else's expert advice, why ask in the first place?

    He wasn't negative at my post. Its probably because I didn't berate him over something he said that really wasn't all that bad.

    The guy really had a valid question about programming using T-SQL that didn't involve set versus procedural honestly. Theres no question that set based coding has advantages, and all he has to do is google something like "slow cursor sql" and he doesn't have to post a question anywhere.

    Speaking of tone, don't you think yours was a bit sour?

    And at what point did the original poster actually take anyone's advice?   And yet you see no problem with their tone?   Maybe you are willing to just ignore those facts, but I cannot and will not.  It was quite clear from their comments that any solution that was anything other than exactly what they wanted to hear just wasn't worth their time.   Sorry, but I'm not willing to try and help someone who's looking for validation of a not so great idea and does nothing but ignore everyone's advice.   Call it sour if you want to...   I just call 'em as I see 'em.

    If all he wants is validation he came to the wrong site.  As frustrating as it can get we should all try to provide the best answers possible, and if that means pointing out why the way they are trying to do things is wrong then that is what we should do.  If they don't like it, they can go to StackOverflow where such discussions are vigorously discouraged.

  • November 14, 2017 at 8:37 am

    #1967989

    I actually did take some bits of advice for some direction while others I ignored and I appreciated all the insight.  Some advice was just not helpful but when I use a forum it's for theory on different ways of doing things...and I got that.  Sorry to those who felt I did not provide enough information to help...considering I DID get what I needed, well I'm just thankful some did.  I think personal attacks are unnecessary and assumptions...we know what they say about those so I'll happily look elsewhere for help in the future.

  • November 14, 2017 at 8:41 am

    #1967994

    mcraig 42832 - Tuesday, November 14, 2017 8:37 AM

    I actually did take some bits of advice for some direction while others I ignored and I appreciated all the insight.  Some advice was just not helpful but when I use a forum it's for theory on different ways of doing things...and I got that.  Sorry to those who felt I did not provide enough information to help...considering I DID get what I needed, well I'm just thankful some did.  I think personal attacks are unnecessary and assumptions...we know what they say about those so I'll happily look elsewhere for help in the future.

    If you really want to learn more about SQL Server and become better at it, this is the place.  Most other sites do not encourage dialog and exploration of other ways of accomplishing a task.

  • November 15, 2017 at 3:52 am

    #1968145

    How about another approach?
    You may create not only #tables, but #procedures as well.

    drop procedure testmain
    go
    create procedure testmain
    as

    exec #test1_variant;

    go
    --    the part about is statis and is not affected by any changes in variables

    --    now's the variable part:
    declare @sql nvarchar(4000), @VariantName nvarchar(20)
    set @VariantName = 'variant2'

    set @sql = N'
    create procedure #test1_variant
    as

    select 1, ''hi from test1_' + @VariantName + ''''
    print @sql
    exec (@sql)
    go

    --  Now- the actual execution. It's also pretty static
    exec testmain

    _____________
    Code for TallyGenerator

  • November 15, 2017 at 10:54 am

    #1968236

    Lynn Pettis - Tuesday, November 14, 2017 8:11 AM

    sgmunson - Tuesday, November 14, 2017 7:06 AM

    patrickmcginnis59 10839 - Thursday, November 9, 2017 3:43 PM

    As to your "Why cursors? Well why not." comment, there's a really simple answer: PERFORMANCE. Cursors just can't perform very well as they are what we call RBAR (row by agonizing row). A properly designed set-based solution generally smokes a cursor on performance. So therefore, I'd say "Cursors? You must be kidding!"

    Reading the OP's post it looks like his main concern wasn't performance but probably more like complexity with the addition of having to perform repetitive operations on a list of database targets. I'm not beating up on performance, but that doesn't look to be in his list of concerns.

    I've had to bail to loops before. I have not found a set oriented method to versioning database installations for instance because each test needs a new connection and you cannot (easily?) do set oriented connections, and even if I could, it wouldn't seem to be of any profit unless I had millions of database servers and then I'd spend the effort to marshall some other programming resources to do some threading or something . If you read his post in depth, you would have noticed he's describing looping over databases and performing operations on each one. He WANTED to loop. Warn him about cursor performance once maybe, sure why not, but the extent that happened in this thread just seems so disagreeable.

    As to your statement on "it was an interesting exercise for me", I've got a rather different perspective. Your tone from the very beginning was making it clear that you really aren't very interested in any idea that doesn't somehow strike your fancy or comport with your procedural world-view. If you're going to ask for help from volunteers, at least take the time to understand why cursors are a bad idea instead of just dismissing any nay-sayers. After all, if you're not going to take anyone else's expert advice, why ask in the first place?

    He wasn't negative at my post. Its probably because I didn't berate him over something he said that really wasn't all that bad.

    The guy really had a valid question about programming using T-SQL that didn't involve set versus procedural honestly. Theres no question that set based coding has advantages, and all he has to do is google something like "slow cursor sql" and he doesn't have to post a question anywhere.

    Speaking of tone, don't you think yours was a bit sour?

    And at what point did the original poster actually take anyone's advice?   And yet you see no problem with their tone?   Maybe you are willing to just ignore those facts, but I cannot and will not.  It was quite clear from their comments that any solution that was anything other than exactly what they wanted to hear just wasn't worth their time.   Sorry, but I'm not willing to try and help someone who's looking for validation of a not so great idea and does nothing but ignore everyone's advice.   Call it sour if you want to...   I just call 'em as I see 'em.

    If all he wants is validation he came to the wrong site.  As frustrating as it can get we should all try to provide the best answers possible, and if that means pointing out why the way they are trying to do things is wrong then that is what we should do.  If they don't like it, they can go to StackOverflow where such discussions are vigorously discouraged.

    I don't really think he came for validation, but I think he could have gotten better discussions somewhere else too. I think the SQL community could do with a discussion site with decent moderation. I don't know if Stack Exchange is it because they have some issues also, but I like how they crowdsource their moderation some.

  • November 27, 2017 at 7:57 am

    #1969530

    Ignoring the posts which became a flame war.

    Reading between the lines, I think to OP is mildly frustrated about the fact that sprocs can't take tables as parameter variables. As far as I am aware there are two ways around this.

    The first would be to use global ##temp_tables (note the double ##).  A single #temp_table is scoped to its SPID, but global ##temp tables will exist for as long as any process using it it running after the session that created it was killed.  This means that you can create a Global temp table, Call the stored procedure that references it and then drop the table from tempdb.  If you want to create the table name dynamically then you can do so and pass the table name as an argument to the stored procedure that will then need to execute dynamically generated SQL with all the risks and overheads of SQL injection and query recompile.  I have used this technique where I had a generic email sending process (which requires a CURSOR) and used different sprocs to build the ##temp_email table and then called the usp_send_emails

    The second would be to create a complex object (XML) which you can pass through as an argument and then deconstruct inside the stored procedure back into its own temp/in-memory tables.  This is probably safer and would work better if the procedure is being called from a business logic layer but the programming overhead for seializing and deserializing the XML is expensive (compute should not be too bad but writing x-query is horrible ) .  I presume you could do the same with JSON in 2016 and above but have not tried it.

Viewing 11 posts - 16 through 25 (of 25 total)

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