February 16, 2006 at 8:09 am
Note: There are some extremely simplistic examples listed below for illustration purposes only. Please, no questions about why I want to do that or instructions on how to do it with one procedure.
I have two stored procedures that return record sets. Both need to be accessible from a .NET web service and from Crystal Reports via ODBC. That part I can handle. However, I also need to use the data returned by the first stored procedure in the second stored procedure. (I’d like to do it using a select statement.) I found one way of doing it. There are probably better ways, and I’m hoping there’s something simple I don’t know about.
Any help would be appreciated.
Consider the following example:
Table Test_01:
col0 col1
---- -----------
A 5
A 3
B 4
B 7
A 37
B 4
B 3
CREATE PROCEDURE dbo.spu_Test_A AS
Select * From Test_01
GO
CREATE PROCEDURE dbo.spu_Test_B AS
CREATE TABLE #temp (
[col0] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col1] [int] NULL
) ON [PRIMARY]
Insert Into #temp (col0, col1)
Exec spu_Test_A
Select col0, Sum(col1) as col1 From #temp Group By col0
GO
Exec spu_Test_B
(7 row(s) affected)
col0 col1
---- -----------
A 45
B 18
(2 row(s) affected)
There has to be a better way than using a temporary table.
February 16, 2006 at 8:47 am
Hello Jeff,
You can modify the second stored procedure to include only the select statement and there is no requirement for the temporary table.
You comment out the temporary table creation statement, insert statement and compile the second stored procedure. then you execute the stored procedure which gives the desired result.
Thanks and have a nice day!!!
Lucky
February 16, 2006 at 9:19 am
How do I group (or whatever) on the record set returned?
CREATE PROCEDURE dbo.spu_Test_B AS
Exec spu_Test_A
Select col0, Sum(col1) as col1 From ????? Group By col0
GO
How do I reference the record set returned by spu_Test_A?
February 17, 2006 at 7:48 am
Can you use a view instead of a stored proc for spu_Test_A?
CREATE VIEW vw_Test_01 AS SELECT col0, col1 FROM Test_01
February 17, 2006 at 8:02 am
<< Can you use a view instead of a stored proc for spu_Test_A? >>
I've got about 20,000,000 (and growing) records I'm dealing with. I want to send parameters to spu_Test_A so that it only returns about 10,000 records. Also, I do some joins and calculations in that procedure.
Please correct me if I'm wrong, but if I use a view, won't it make the calculations on all 20,000,000 records?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply