WHAT ARE THE OPTIONS do i have to pass a table from  from one sp to
another?

write the data into a table and then use it from the other won't do the
job for me.


thanks

ken

SQL Ken wrote:
Without writing the table into either a #temp table or work table, you
have no options.

You asked for options:

1. Combine the sprocs so you don't have to pass anything
2. Rewrite your queries so that trying to pass a table is unnecessary.
3. Create a Table Valued Function (TVF) that is used by both sprocs.
4. Declare 23,999 variables and pass them as parameters (choose your own
number...)
5. Write the data out to a file and then read the file back in
or.
6. Create and use a #Temp table

With SQL Server 2005, you could create a xml datatype variable, and then
pass that as a ntext parameter, converting back to xml.

See Ward's article, the 'sidebar' at the bottom:

http://www.microsoft.com/technet/technetmag/issues/2005/05/SQLXML/default.aspx


Show quoteHide quote

ntext is deprecated and "will be removed in a future version of SQL Server"
according to BOL.  Maybe nvarchar(max)?

I think you're right about using varchar(max) instead.

Show quoteHide quote

You can also declare parameters to your SP's as xml data type without
casting to a character type

Arnie,
Thought of all your suggested but didn't work.
Can't use a Temp table because it get detroyed after the sp is
executed.
can't pass a table into a function.
can't write data into a table and read it in from another becuase,
multiple users can execute the sp's at once.
Can't combine the sp's becuae they are in two diffent servers and I
conect to the remotely.
can't use open query, becaue i can't pass in the parameters.

I am not familiar with XML datatype but will look into that as an
option. However, my srver is 2000.

Your input is highly appreciated
Ken.

You could add a column or two and include the spid, or system_user(), both
of which are unique to the user, and known to any sproc.

SQL Ken wrote:
If you create the temp table in the calling procedure, then it will be
available to the called procedure(s) and still exist afterwards (until
you drop it or the calling procedure exits).

e.g.

Proc1:

CREATE TABLE #temp1(...)

INSERT #temp1....

EXEC Proc2

SELECT * FROM #temp1


Proc2:

UPDATE #temp1.....
etc

Chris

Thanks,
I will try this

Ken
Chris Lim wrote:
Show quoteHide quote

For some options, see:
http://www.sommarskog.se/share_data.html

Update Statement
Date Range
UNDO
XP_SENDMAIL Overload in SQL 2005
how to do this