Passing a Table to A Stored Procedure
-
It's an interesting approach, I'll have to try it sometime. The approach we've used in the past is to define a permanent table, and add a 'session' GUID key on the table. The first sproc populates the table, updating the 'session' column using a GUID value representing this session. It then calles the second sproc, passing the session key, so that the second sproc knows which rows to read from the table.
A drawback is that the second sproc has to delete the rows when it is done with the table. However, it is useful to have the rows in the table when you are troubleshooting.
-
I don't think the XML solution is "bad" per se, in no way did the author claim this was suited to all situations. The trigger solution will have its own set of problems. We all know there's never a single silver bullet - that's why there are so many different approaches. And for someone (Sergiy) to think those of us on this forum needed a quote from BOL to know what a trigger is, then well... the XML solution is probably over that person's head.
We've been using the XML solution in my group for a few years (2K uses sp_xml_preparedocument) and it works very well. Another benefit is that there are no dummy objects which could require additional maintenance, which becomes a big deal over time.
Great job Jacob - you've highlighted and very clearly explained one of those little-known insider tricks so that many more people can take advantage of it.
-
Dale,
That's a nice solution. You could add a column to datetime stamp the rows and have a sqlagent job clean up all old sessions later to prevent having to add clean up code to all of your sprocs.
JimFive
-
Jacob,
Nice article and another way to do something we all have had to invent a way around in our T-sql careers
These are some of the others I have seen.
1. A global temp table ##xx get's around the scope issue ofcourse it must be a unique name or unique column to key off of for multi use but it works.
2. Concatenation. Pass in a string and a delimiter and have fun with a while loop.
3. The tried and true staging table not as "hot" of a solution but has a place to.
I guess we can now add XML and triggers to the list anyone else have a magic wand idea out there not covered?
Maybe someone can get jiggy with a sp_OACreate and an array, or how about two cans and a string?
Me I'm headed to the river.
Enjoy,
M
-
Bad judgement cause by lack of knowledge.
There gonna be as many "inserted" tables as many triggers called by different users. One per trigger being executed. Not visible from anywhere else.
No kabooms.
Read some books (BOL for a start) and get some practical experience with triggers before you say.
_____________
Code for TallyGenerator -
Can you name one problem of trigger solution?
And I pretty sure majority of people submitted posts here never read the statement I quoted. And they afraid of using triggers because they just don't know it's just a SP and it's "probably over that person's head".
I have a chance to compare both approaches.
It takes about 1 hour just to upload 15 MB XML file into SQL2000 server when same amount of data being uploaded AND PROCESSED via trigger on view within minutes.
And it's only political reasons what force us to keep XML processing.
Carie, what did you mean talking about "dummy objects which could require additional maintenance"?
If you meant that view then read my post again and find out that it's just table definition. If you want to pass a table you need to define it some way. Don't you?
_____________
Code for TallyGenerator -
Jacob,
Thank you, I've been trying to get some XML practice and I found your article very helpful.
I realize this statement opens me to being flamed for my ignorance by the illuminazi(s) who flamed you but such is life.
[font="Comic Sans MS"]Tom Powell
http://philergia.wordpress.com/[/font] -
If the whole point is to find some use for XML then, well, it's quite useful.
But if you are looking for best solution then using of XML does not make any sense.
I cannot imagine any worse approach than using XML for such task.
Can you suggest one?
_____________
Code for TallyGenerator -
You can update any kind of view if you have appropriate INSTEAD OF trigger on it.
Actually in such case UPDATE will not do anything but invoke the trigger code.
What will happen to the data inserted/deleted depends on what you put in the code.
_____________
Code for TallyGenerator -
Thanks jacob,
your Explanation is very much clear and your example is very Explicable.
keep writing more and more article....
-
One thing no-one seems to point out:
Why not ASK MICROSOFT and other DBMS vendors to build in Table-Vars as fully fledged things?! Why are we stuck with XML work-arounds (which I too use) when a relational system should handle relational-freakin'-variables?
It's like an algebraic calculator without parentheses or variables.
Please, if the opportunity presents itself, plead with the vendors to make more useful products.
-
What's wrong with #tables?
You can populate one or more tempopary tables and call any SP on any database within the same server. These tables will be visible for that SP.
If you need to pas a table to SP on another server you can use ## table(s).
So, the only thing I see to be asked is "RTFM!" to developers.
_____________
Code for TallyGenerator -
Other than about an order-of-magnitude performance hit (due to saving to disk), not too much.
But they are also not true variables and don't have rigorous scoping rules, etc. Global temp-tables aren't usually a good solution to the scoping issues.
Another thing "true" table-vars would solve is to be able to define a function that returns a table where the structure is determined at run-time. An old (pre-SQL) relational language allowed that, which allowed things like extending relational operators without waiting for the vendor. People were able to define a relational "divide" operator without resorting to Celko-style SQL work-arounds. (Although those are clever, they shouldn't be necessary.)
My point is vendors don't have the goal of making a relational system work well. They have the goal of following market fads and trends (like XML) so they can put cool labels on the retail box and brochure. Kudos to people who figure out good work arounds (like this article points out), but in many cases these shouldn't be necessary.
(True) Table variables get around a lot of these headaches and would enable robust passing of SETS of data between client and server, not just row-sets passed one direction. No more kludgy faked-out "batch update" garbage which actually does row-by-row iterations, etc.
We shouldn't be so blind to glaring gaps in products just because it comes from Redmond-on-high, or Oracle, or anywhere else.
(If your dig at developers was aimed at me, I'm a 10+ year DBA, not a developer.)
-
Eric, nice post.
1) I agree in theory that ability to pass a table variable would add value to a lot of processes.
2) I bet a lot more table variables get written to tempdb under the covers than you probably suspect due to internal memory pressure.
3) I agree wholeheartely with your comment about RDBMs vendors following the herd and throwing XML into every damn thing. Lots of developers use the latest/greatest spiffy technology out there despite the fact that is it patently a BAD solution to their problem at hand. I despise that mentality personally!
4) I do think there would be significant developement on MS's part to implement robust table variables, especially the ability to pass them around objects. I am not convinced it would be worth it, given other things I would prefer them to work on.
5) Disagree with you on undefined-table-structure output from functions. Not a good idea due to compile/optimization/query plan issues, IMHO. BTW, I think I have had to use a true 'relational divide' operation ONCE in my 15 years as a database specialist.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Eric, you need probably read some articles (including topic in BOL) about temp tables and table variables.
Temp table is created in memory and stays there until it's too big. Then it's being written to disk.
Table variable is created in memory and stays there until it's too big. Then it's being written to disk.
Table variable creates the same record in tempdb system catalogue as temp table.
Every hash join, correlated query, subquery in badly designed systems creates #table in tempdb. You just don't see it.
So, what "order-of-magnitude performance hit" are you talking about?
Can you name 1(one) problem which you want to resolve with table variables and which is not resolved with #tables?
> An old (pre-SQL) relational language allowed that, which allowed things like extending relational operators without waiting for the vendor.
And where are those "old (pre-SQL) relational languages"?
Abandoned and forgotten. Because they lost competition to SQL. They just cannot deliver acceptable performance on reasonable amounts of data.
And I agree with your statement about XML. I believe my 1st post here did not leave any doubts about that. One of the most useless features in relational database.
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 100 total)
You must be logged in to reply to this topic. Login to reply