I've got 2 stored procedure and I'd like to join the result of the 2 stored
procedure using another stored procedure but without using temporary tables
I'm using SQL Server 2000
How can I do it?
Help me please!!!!
VAnessa
by Vanessa Bern » Thu, 10 Jan 2002 19:37:39
I'm using SQL Server 2000
How can I do it?
Help me please!!!!
VAnessa
by Dejan Sark » Thu, 10 Jan 2002 20:09:09
there is no direct method in SQL Server to use the SP's rowset in outer
Select (i.e. it's not possible to write Select ... From Exec SP). But in SQL
2000 you have User-Defined Functions (UDFs). Do check if you can change your
SPs to UDFs.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
Quote:> I've got 2 stored procedure and I'd like to join the result of the 2
stored
> procedure using another stored procedure but without using temporary
tables
> I'm using SQL Server 2000
> How can I do it?
> Help me please!!!!
> VAnessa
by Vanessa Bern » Thu, 10 Jan 2002 20:21:33
Sorry for my ignorance but I don't know what UDFs are.
I've got another question.
It is inefficient If I simply use temporary table to memorize the
results of the 2 stored procedure and then execute the join?
thanks
Vanessa
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
by Dejan Sark » Thu, 10 Jan 2002 20:34:36
do please read the "User-Defined Functions" topic and subtopics (specially
"User-Defined Functions That Return a table Data Type") in Books OnLine.
Using temp tables with SPs can be less efficient than using UDFs.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
Quote:> Sorry for my ignorance but I don't know what UDFs are.
> I've got another question.
> It is inefficient If I simply use temporary table to memorize the
> results of the 2 stored procedure and then execute the join?
> thanks
> Vanessa
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
by PPS » Fri, 11 Jan 2002 02:38:21
It may be more efficient to use User-defined functions IF your stored
procedures can be converted to UDFs ( there are some restrictions ) and if
you are using SQL Server 2000.
HTH.
Quote:> Sorry for my ignorance but I don't know what UDFs are.
> I've got another question.
> It is inefficient If I simply use temporary table to memorize the
> results of the 2 stored procedure and then execute the join?
> thanks
> Vanessa
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
by Murali [M » Fri, 11 Jan 2002 04:53:52
It is perfectly Ok to use temp tables and do a join on them, however i like
the idea of using the UDFs, this is ofcourse provided you are on SQL 2K and
you could convert the sprocs to UDFs.
Thanks,
Murali.
1. Problem with stored procedure that calls other stored procedures
I wrote a stored procedure that creates a temporary table and then calls a
series of other stored procedures to insert rows into the table. Finally
it selects all columns from the temp table. I tested it in InterDev and
everything was fine.
The problem came when I fired up Crystal Reports and tried to create a
report based on the stored proc. I get an error message: "Error opening
file. There are no fields in the file: proc_mfpri_report" .
(proc_mfpri_report is my stored proc). When I replace the calls to other
procs with inline code it works fine, but I hate not being able to
modularize my code. I have tested using a "permanent" table instead of a
temporary one, but it makes no difference.
Has anyone seen something like this?
Much obliged,
Geoff Pennington
2. async i/o causes server to hang??
3. Problem compiling stored procedure that calls another stored procedure
4. Error: ConnectionRead (recv()).
5. PROBLEMS ON STORED PROCEDURES (Procedure not found)
7. problems with stored procedure parameters
8. MS-Access Left-Function and SQL
9. Problem running stored procedure by SQL Server Newbie
10. Problems of Stored procedure
11. Named Pipe problem in stored procedure
12. Problem w/ stored procedure execution from MS-Access