|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Passing a Table var as an input/output parameterHi 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
http://www.sommarskog.se/share_data.html
Show quote "João Costa" <JooCo***@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 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.... Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > http://www.sommarskog.se/share_data.html > > > > "João Costa" <JooCo***@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 > > > > Thanks Aaron, but that didn't help, I need to pass the stored procedureS I think you are stuck on the "need to pass...parameter" solution instead of > as > parameterS 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 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! Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > 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 > > > > 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.The most interesting part of the subject is that it works in SYBASE.
Ok, thanks a lot for your help. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > 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. > > > > 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. No, you have to do that with a temp table or a delimited string (if possible).
HTH, Jens Suessmeyer. Show quote "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 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?? Show quote "Jens Süßmeyer" wrote: > 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  |
|||||||||||||||||||||||
Other interesting topics