I have a rather complex storeped proc that does many calculations and
returns a row of data. Now I need to take that result set and use it along
with another result set by way of a select statement. A simplified example:
exec sp_GetAmounts @.ID
returns: 100, 300, 400 , 500
select * from BK where BKID = @.ID
returns: Joe Scmoe, 1234 Main Street, 90-32920, 01/01/2005
Now Ideally, I'd like to have something that 'marries' the two queries and
returns:
Joe Scmoe, 1234 Main Street, 90-32920, 01/01/2005, 100, 300, 400 , 500
My real select statement is more complex than the above example, involving
several joins. Both results sets return much more data than in the above
examples.
Is this possible?You can grab the sp resultset in a temporary or normal table, and use it to
join with the result of the select statement.
Example:
use northwind
go
create table #t (
ShippedDate datetime,
OrderID int,
Subtotal money,
[Year] int
)
insert into #t
exec dbo.[Sales by Year] @.Beginning_Date = '19960101', @.Ending_Date =
'19961231'
select
oh.orderid, oh.orderdate,
t.[year],
t.subtotal
from
orders as oh
left join
#t as t
on oh.orderid = t.orderid
and oh.orderdate >= ltrim(t.[year]) + '0101'
and oh.orderdate < ltrim(t.[year] + 1) + '0101'
drop table #t
go
AMB
"Nelson F." wrote:
> I have a rather complex storeped proc that does many calculations and
> returns a row of data. Now I need to take that result set and use it along
> with another result set by way of a select statement. A simplified example
:
> exec sp_GetAmounts @.ID
> returns: 100, 300, 400 , 500
>
> select * from BK where BKID = @.ID
> returns: Joe Scmoe, 1234 Main Street, 90-32920, 01/01/2005
>
> Now Ideally, I'd like to have something that 'marries' the two queries and
> returns:
> Joe Scmoe, 1234 Main Street, 90-32920, 01/01/2005, 100, 300, 400 , 500
>
> My real select statement is more complex than the above example, involving
> several joins. Both results sets return much more data than in the above
> examples.
> Is this possible?
>
>|||Why not use a function instead
For example
USE Northwind
GO
CREATE FUNCTION MyFunc (@.CustId varchar(5))
RETURNS TABLE
AS
RETURN (SELECT CustomerId, Count(OrderID) NoOfOrders, MAX(OrderDate) AS
LastOrdered
FROM Orders
WHERE CustomerId = @.CustId
GROUP BY CustomerId)
GO
CREATE PROC MyProc @.CustId varchar(5)
AS
SELECT CT.CustomerId, CT.CompanyName, FN.NoOfOrders, FN.LastOrdered
FROM dbo.MyFunc(@.CustId) FN INNER JOIN Customers CT
ON FN.CustomerId = CT.CustomerId
GO
EXEC MyProc 'VINET'
"Alejandro Mesa" wrote:
> You can grab the sp resultset in a temporary or normal table, and use it t
o
> join with the result of the select statement.
> Example:
> use northwind
> go
> create table #t (
> ShippedDate datetime,
> OrderID int,
> Subtotal money,
> [Year] int
> )
> insert into #t
> exec dbo.[Sales by Year] @.Beginning_Date = '19960101', @.Ending_Date =
> '19961231'
> select
> oh.orderid, oh.orderdate,
> t.[year],
> t.subtotal
> from
> orders as oh
> left join
> #t as t
> on oh.orderid = t.orderid
> and oh.orderdate >= ltrim(t.[year]) + '0101'
> and oh.orderdate < ltrim(t.[year] + 1) + '0101'
> drop table #t
> go
>
> AMB
> "Nelson F." wrote:
>|||> Why not use a function instead
I can not answer this question because I have no idea what the sp is doing.
No code was posted with the msg.
AMB
"Andy B" wrote:
> Why not use a function instead
> For example
> USE Northwind
> GO
> CREATE FUNCTION MyFunc (@.CustId varchar(5))
> RETURNS TABLE
> AS
> RETURN (SELECT CustomerId, Count(OrderID) NoOfOrders, MAX(OrderDate) AS
> LastOrdered
> FROM Orders
> WHERE CustomerId = @.CustId
> GROUP BY CustomerId)
> GO
> CREATE PROC MyProc @.CustId varchar(5)
> AS
> SELECT CT.CustomerId, CT.CompanyName, FN.NoOfOrders, FN.LastOrdered
> FROM dbo.MyFunc(@.CustId) FN INNER JOIN Customers CT
> ON FN.CustomerId = CT.CustomerId
> GO
> EXEC MyProc 'VINET'
>
> "Alejandro Mesa" wrote:
>|||Sorry AMB, i was offering Nelson an alternative solution to yours given the
ouput he had specified.
I should've replied to his message and not yours
Andy
"Alejandro Mesa" wrote:
> I can not answer this question because I have no idea what the sp is doing
.
> No code was posted with the msg.
>
> AMB
>
> "Andy B" wrote:
>|||Thanks to both of you both solutions work well!
by software or humans.
DarrenSQLIS wrote: