How to pass tables by reference in SQL Server Stored Procedures

[YaBabyYa]

Hello,

I have a stored procedure: SP_SPLIT

Input: Input_Word varchar(1000)
Output: Select * from #temp_table

which returns a temporary table with 2 columns:

#temp_table--------------
-------------------------
Word-----------Varchar(80)
Value----------Float



I need to retrieve this query in a Main procedure. Can any one tell me the SYNTAX of how to pass a table by reference.

Here is what I am trying to achieve:

Call Flow Execution

1)Procedure Name: Sp_Main Input: input_word varchar(1000)

2)SP_Main calls SP_Split: #table_split=SP_SPLIT input_word

2a) SP_Split returns a table #temp_table

3) SP_Main creates a table:

Create Table #table_result {Word_ID (int), Value (float)}


4) SP_Main cursor loops for each row in #table_split
..do some work
Insert value(s) in #table_result

return Select * from #table_result




I require a code snippet for step #2 and step #2a

Thanks.

 

[bborissov]

You can't.
1.Procedure Name: Sp_Main Input: input_word varchar(1000)
2.create temp table #temp_table
3. INSERT INTO #temp_table
EXEC yourSP input_word
4. Select * from #table_result

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[YaBabyYa]

I am trying to achieve the equivalent of a function call with an array result returned as a multi-column temporary table to a main procedure.

main_function ( input_word (string) )

Table_1 [ ]= another_function ( input_word )

Table_2 [ ]= empty return result set

While (Table_1 .hasMoreRows() )

Do some work with Table_1
Insert in Table_2[ ]
i++


return Table_2[ ]

 

[ESquared]

See "passing a list of values to a stored procedure" parts 1, 2, and 3, in the FAQs in this forum.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[SQLSister]

And BTW never name your proc starting with SP. This is for system procs. SQL server will check for a system proc first and then execute,plus if Microsoft ever decides to use the same name, your proc will never execute.

"NOTHING is more important in a database than integrity." ESquared