September 2, 2004 at 9:33 am
Is this possible?
Can you pass a table variable to another stored procedure, modify its contents, and then return it to its original location? If so, what syntax should I be using?
Thanks in advance.
September 2, 2004 at 9:58 am
You can create a user defined function that returns a table variable. You can also JOIN on a fuction. But you can't pass a table variable to a stored procedure or function.
September 2, 2004 at 10:05 am
Will a function accept a table as input?
September 2, 2004 at 10:28 am
A function cannot receive a table as input. This type of functionality (passing tables and returning tables from stored procedures) is usually accomplished with a connection-level temporary table (#MyTable). If there is an alternative strategy, I would love to find it!
Bob Spruill
September 3, 2004 at 10:19 am
Carl, you've hit upon one of my biggest pet peeves with SQL Server and other products... so-called "relational" systems that do not allow decent manipulation of relational variables (a.k.a. tables).
I asked about support for this at the last SQL PASS and the SQL Server team made comments to each other like "Did that make it into Yukon?"
The bad news is I don't believe it's in the next version either (although I am not certian).
But the good news is it seemed evident that the MS developers are aware of the gap and are giving thought to how to solve it.
September 3, 2004 at 11:15 am
Eric,
Yukon kind of solves this; sprocs can accept cursors as input. I agree with you, though, SQL products should support relvars as intrinsic types rather than as pure containers. This would bring the products a lot closer to being truly relational...
--
Adam Machanic
whoisactive
September 3, 2004 at 11:56 am
That's good to know. Thanks.
Although it does seem a bit of an odd choice. It will be fun seeing a "practical" example where it's useful.
...
I should have mentioned earlier: I have done work-arounds to this for 1- or small-dimensioned arrays by using a "List-to-Table" UDF.
The interface to the sproc allows a delimited list. In the procedure I convert that to a table-variable using a "list-to-table" function. Then I join to that table.
It works very well for one-dimensional lists. Because the interface to the UDF cannot be dynamic, I handle n-dimensional a bit oddly. I first split out the "rows" but any "columns" are still concatenated. Then I issue an UPDATE to the table variable to split column values out into separate columns.
In this second case, Yukon's ability to issue a UDF for each row in a SELECT would be helpful because I could essentially use the "List-To-Table" routine to parse out the column values as well.
No doubt it's a kludge. But it's something.
On the "true-relational" - I hear you!
Imagine: an "algebraic" calculator whose statement results cannot be used as inputs to further statements. Or that let's you assign values to variables, but not reference those variables in algabraic statements.
Well, folks, that's what we have with SQL Server, Oracle, and the like-- basically ANSI SQL systems, not Relational systems. You cannot select (directly) from stored procedures; you can UNION select statements, but not tables or views; you cannot pass a 'table-variable' in and out of routines.
We *should* be able to do all those things. Hopefully time will bear out these desires.
September 3, 2004 at 12:07 pm
By the way, this article shares some methods:
http://www.sommarskog.se/share_data.html
--
Adam Machanic
whoisactive
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply