Home > Archive > SQL Server Programming > July 2005 > Passing a Table var as an input/output parameter
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Passing a Table var as an input/output parameter
|
|
| João Costa 2005-07-28, 9:11 am |
| Hi all
About Table vars, is it possible to pass it to sub Stored Procedures as a
parameter or not?
declare @MDMB table(
MIDIIC smallint not null,
MRCDDAA smallint not null,
MRCDDAM smallint not null,
MRCRIC char(1) not null,
MDMBIY char(6) not null )
couldn' figure out the Syntax,
Thanks in advance
| |
| Aaron Bertrand [SQL Server MVP] 2005-07-28, 9:11 am |
| http://www.sommarskog.se/share_data.html
"João Costa" <JooCosta@discussions.microsoft.com> wrote in message
news:410DA0C5-9781-491B-8736-0369C0ACAF4A@microsoft.com...
> Hi all
>
> About Table vars, is it possible to pass it to sub Stored Procedures as a
> parameter or not?
>
> declare @MDMB table(
> MIDIIC smallint not null,
> MRCDDAA smallint not null,
> MRCDDAM smallint not null,
> MRCRIC char(1) not null,
> MDMBIY char(6) not null )
>
> couldn' figure out the Syntax,
>
> Thanks in advance
| |
| Jens Süßmeyer 2005-07-28, 9:11 am |
| No, you have to do that with a temp table or a delimited string (if possible).
HTH, Jens Suessmeyer.
"João Costa" wrote:
> Hi all
>
> About Table vars, is it possible to pass it to sub Stored Procedures as a
> parameter or not?
>
> declare @MDMB table(
> MIDIIC smallint not null,
> MRCDDAA smallint not null,
> MRCDDAM smallint not null,
> MRCRIC char(1) not null,
> MDMBIY char(6) not null )
>
> couldn' figure out the Syntax,
>
> Thanks in advance
| |
| João Costa 2005-07-28, 9:11 am |
| Thanks Aaron, but that didn't help, I need to pass the stored procedureS as
parameterS so I Cannot return only one table. But I'm sure there must be a
way...
.... Or not....
"Aaron Bertrand [SQL Server MVP]" wrote:
> http://www.sommarskog.se/share_data.html
>
>
>
> "João Costa" <JooCosta@discussions.microsoft.com> wrote in message
> news:410DA0C5-9781-491B-8736-0369C0ACAF4A@microsoft.com...
>
>
>
| |
| Aaron Bertrand [SQL Server MVP] 2005-07-28, 9:11 am |
| > Thanks Aaron, but that didn't help, I need to pass the stored procedureS
> as
> parameterS
I think you are stuck on the "need to pass...parameter" solution instead of
focusing on what you are actually trying to accomplish (which is sharing
data between two procedures, not specifically passing a table variable as a
parameter, which CANNOT BE DONE).
For example, you can easily create a #temp table in procedure a, and then
use it in procedure b.
CREATE PROCEDURE dbo.ProcB
AS
BEGIN
SELECT * FROM #foo
END
GO
CREATE PROCEDURE dbo.ProcA
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #foo(a INT)
INSERT #foo SELECT 1 UNION SELECT 2
EXEC dbo.ProcB
DROP TABLE #foo
END
GO
EXEC dbo.ProcA
GO
DROP PROCEDURE dbo.ProcA, dbo.ProcB
GO
| |
| João Costa 2005-07-28, 5:04 pm |
| Ok since you mention temp tables, it might suit ethe need. I have a question
about it: Is it possible to declare a temporary table inside a transaction
or there is there any inconvenience??
"Jens Süßmeyer" wrote:
[color=darkred]
> No, you have to do that with a temp table or a delimited string (if possible).
>
> HTH, Jens Suessmeyer.
>
> "João Costa" wrote:
>
| |
| João Costa 2005-07-28, 5:04 pm |
| I see you point, but the reason I chose temp vars was because I'm migrating a
set of HUGE stored procedure from Sybase to Sql2000. In Sybase they work well
but in Sql2000, after all the processing has been done and 3 transactions
have been rolled back when the code returns to one of the calling SPs I get a
call stack error, I know it's a lot of data being passed back and forth. The
message looks like this:
Server: Msg 3701, Level 11, State 5, Procedure PCRMQDD00vInt, Line 771
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table
'#TRMCQDL0', because it does not exist in the system catalog.
Failed to get the call stack!
"Aaron Bertrand [SQL Server MVP]" wrote:
>
> I think you are stuck on the "need to pass...parameter" solution instead of
> focusing on what you are actually trying to accomplish (which is sharing
> data between two procedures, not specifically passing a table variable as a
> parameter, which CANNOT BE DONE).
>
> For example, you can easily create a #temp table in procedure a, and then
> use it in procedure b.
>
> CREATE PROCEDURE dbo.ProcB
> AS
> BEGIN
> SELECT * FROM #foo
> END
> GO
>
> CREATE PROCEDURE dbo.ProcA
> AS
> BEGIN
> SET NOCOUNT ON
> CREATE TABLE #foo(a INT)
> INSERT #foo SELECT 1 UNION SELECT 2
> EXEC dbo.ProcB
> DROP TABLE #foo
> END
> GO
>
> EXEC dbo.ProcA
> GO
>
> DROP PROCEDURE dbo.ProcA, dbo.ProcB
> GO
>
>
>
| |
| Aaron Bertrand [SQL Server MVP] 2005-07-28, 5:04 pm |
| > call stack error, I know it's a lot of data being passed back and forth.
None of this changes the fact that you CAN'T PASS A @TABLE AS A PARAMETER.
| |
| João Costa 2005-07-28, 5:04 pm |
| The most interesting part of the subject is that it works in SYBASE.
Ok, thanks a lot for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
>
> None of this changes the fact that you CAN'T PASS A @TABLE AS A PARAMETER.
>
>
>
| |
| Aaron Bertrand [SQL Server MVP] 2005-07-28, 5:04 pm |
| > The most interesting part of the subject is that it works in SYBASE.
Yep. SYBASE != SQL Server. There are a lot of things that work in Oracle
and not in SQL Server, too, never mind the reverse. In general, they call
these things differentiation, and if they are on the top of your list,
you'll choose the products that support these features. If you choose a
different product that doesn't support these features, then you find a
different way to accomplish the same outcome, or approach the problem in a
different way, instead of forcing a square peg into a round hole.
|
|
|
|
|