July 21, 2011 at 8:19 am
I'd also suggest reading article in link below. This is exhausting description of all possible options which you can use for sharing data between procedures. I really spent a lot of time of trying to practically use suitable of them for my scenario but end with process-keyed tables. All other solution had serious maintenance or performance issues (in my case). Furthermore, some of them were not "refactoring-friendly" - e.g. posting OUTPUT parameters back and forth throughout many stored procedures leads to refactoring nightmare when you need to modify them.
http://www.sommarskog.se/share_data.html
Jakub
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In
July 21, 2011 at 9:09 am
I'd also suggest reading article in link below. This is exhausting description of all possible options which you can use for sharing data between procedures. I really spent a lot of time of trying to practically use suitable of them for my scenario but end with process-keyed tables. All other solution had serious maintenance or performance issues (in my case). Furthermore, some of them were not "refactoring-friendly" - e.g. posting OUTPUT parameters back and forth throughout many stored procedures leads to refactoring nightmare when you need to modify them.
The SPID-Keyed tables really work (though slightly scarey to contemplate), but you'd surely only need to use them if you ware using 'startup' global temporary tables. Erland is pretty persuasive about avoiding local temporary tables.
Best wishes,
Phil Factor
July 21, 2011 at 9:18 am
This is very timely as I have a stored procedure I'm writing that needs to parse numerous diagnoses and procedures into columns. Should I create separate tables, such as #dxtable, #proctable, or should I make one large table with all 49 diagnoses and 49 procedures in it? How do I handle situations where not all 49 columns will be populated?
Thanks,
Sid
July 21, 2011 at 9:33 am
To be honest, I am not sure if I understood well your problem - can you explain it in more detail? It looks that using UDDT as input parameter might be good idea for you, but...?
Jakub
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In
July 21, 2011 at 9:42 am
Hi Jakub,
I have a table containing account numbers and diagnoses. There are multiple records for a given account. I need to parse or normalize this data into columns so that each diagnosis code is placed in a column. I'm creating a temptable in my stored procedure defining fields such as dx1, dx2.....dx49. How do I ensure that the stored procedure loops through and parses the records into each column in my temptable? I cannot use PIVOT because I'm not aggregating anything, just needing to place up to 49 diagnosis codes in their respective columns. I also need to do the same for up to 49 procedure codes from a different table.
Thanks,
Sid
July 21, 2011 at 10:07 am
Sid, it may be better to ask this question on ASK.SQLServerCentral.COM, as it may skew a forum that is really about Jakub Dvorak's article, and you'll get more people looking at it there.
Best wishes,
Phil Factor
July 21, 2011 at 10:29 am
Jakub Dvorak (7/21/2011)
Practical example behind this article was following:I needed to share data among stored procedures because I had very complex task which required to pull data from fixed tables to some temporary structures and do some logic above them. I picked #table solution for sharing data because all others (output parameters, UDDT, ...) were not sufficient for my scenario. Then I've started to "encapsulate" this complex logic and realized that you cannot use #table with function. I made up synonym workaround at this point.
At the end of a day I had to use process-keyed tables for data sharing because this workaround had very big maintenance drawback as described in article.
Jakub Dvorak
If you need to pull some data from temporary structures and get that information to a function, then what you really want is to pass a table-valued parameter to the function. That is the supported way to get complex information into a function.
July 21, 2011 at 11:55 am
Agree, but you must also include performance aspect. If I use solution you described it would mean that for every call of this function I would have to insert data to table-valued variable in order to use it as input parameter for function. It is usable for few records but not for tens of thousands. If you deal with more data you must use some "structures" which are really shared and not passed in and out as function parameters.
Jakub
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In
July 21, 2011 at 12:09 pm
Jakub Dvorak (7/21/2011)
Agree, but you must also include performance aspect. If I use solution you described it would mean that for every call of this function I would have to insert data to table-valued variable in order to use it as input parameter for function. It is usable for few records but not for tens of thousands. If you deal with more data you must use some "structures" which are really shared and not passed in and out as function parameters.Jakub
But you have to insert data into a table anyway, such as in the process-keyed table. If you were able to create temp tables in a function, you would still have to populate them.
I don't understand why this is a problem. Unless you want the function to work on data that's already in some permanent tables, which is really a different problem.
July 21, 2011 at 12:25 pm
See this schematic "code":
1) Pull data from fixed tables to temporary structures (used for sharing) only once
2) Perform complex logic above temporary structures
Point 2) includes calling to many functions and procedures which are using "shared" temporary structures. I have around 20 functions and procedures which encapsulates quite complex logic - I cannot afford to pass shared data in everytime I need to call function/procedure. I need simply to have these data available inside function/procedure in order to avoid performance issue.
So this is all about how these "temporary structures" will be implemented.
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In
July 21, 2011 at 1:31 pm
Jakub Dvorak (7/21/2011)
See this schematic "code":1) Pull data from fixed tables to temporary structures (used for sharing) only once
2) Perform complex logic above temporary structures
Point 2) includes calling to many functions and procedures which are using "shared" temporary structures. I have around 20 functions and procedures which encapsulates quite complex logic - I cannot afford to pass shared data in everytime I need to call function/procedure. I need simply to have these data available inside function/procedure in order to avoid performance issue.
So this is all about how these "temporary structures" will be implemented.
OK, that makes sense. Thanks.
July 21, 2011 at 2:26 pm
This post intentionally left blank.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 21, 2011 at 4:32 pm
Nice find. I really think it's a bug though. And MS will probably fix it in Denali or a later version.
Have you tested this with Denali?
Dennis Parks
MCSE, MCDBA, MCSD, MCAD, MCTS
July 22, 2011 at 12:34 am
Yes, it's working in Denali as well...
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In
July 22, 2011 at 3:53 am
Jeff Moden (7/21/2011)
RichB (7/21/2011)
Presumably that also has the effect of turning the temporary table into a global temporary table that magically disappears as soon as the original session clears it out? :hehe:Bet that could make for some troublesome bughunts.... and some extremely awkward concurrency issues!
No... it wouldn't turn the temp table into a global one anymore than using a call to a temp table from a stored procedure that didn't actually build the temp table, so no concurrency issues.
Ah, yes, now I have had an opportunity to test this. I had imagined the synonym would create a much tighter link to the underlying object (perhaps the full tempdb name), yet it doesn't; as you say the temp table remains within the expected scope, querying the synonym on a different session generates a 'synonym <name> refers to an invalid object'.
Once we create that object in scope and populate it, we do indeed get different results from the same synonym on different sessions.
I am torn between considering this genius or madness...:pinch:
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply