Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

"combine" the results of a stored proc?

Posted on 2013-01-31
4
154 Views
Last Modified: 2013-09-13
Is it possible to take the results of a stored proc and then SUM one of the columns, leaving all the other columns as they are?

The two rows returned are identical except for the amount.

sumexecsp
0
Comment
Question by:knowlton
  • 2
4 Comments
 
LVL 39

Assisted Solution

by:appari
appari earned 250 total points
ID: 38842049
if possible change it in the stored procedure itself, by using grouping. post your existing procedure script here.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 250 total points
ID: 38842736
You can put the results of the SP into a temporary table or table variable through the following syntax:

INSERT INTO #YourTempTable
EXEC ...

This assumes that #YourTempTable already exists.  Once the data is in the temp table, you can use a regular SELECT statement to manipulate it any way you'd like.
0
 
LVL 5

Author Comment

by:knowlton
ID: 38844024
Here is the current T-SQL:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Marketing_Plan_RepairBidDetail_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[Marketing_Plan_RepairBidDetail_Get]

GO

CREATE PROCEDURE [dbo].[Marketing_Plan_RepairBidDetail_Get]

@RepairBidID	int,
@RepairBidSID	int

AS


select 
rb.BidCompany
,rih.RprEstCompletionDays
,subselRU.sumofamt
,rb.BidDocID 
,rb.BidDocSID
,rb.AssetTaskID
,rb.AssetTaskSID
,rb.RepairBidID
,rb.RepairBidSID
,rb.StatusID
From RepairBidForm rbf
join RepairBid rb on rb.RepairBidFormID = rbf.RepairBidFormID 
and rb.RepairBidFormSID = rbf.RepairBidFormSID
join RprInspHeader rih on rb.RepairBidID = rih.RepairBidID
join (select ru2.RepairBidID, ru2.RepairBidSID, sum(Amt) as sumofamt
from RepairUnit ru2
group by 
ru2.RepairBidID, 
ru2.RepairBidSID, 
(Amt)) subselRU on rb.RepairBidID = subselRU.RepairBidID
join RepairItem ri on rbf.RepairBidFormID = ri.RepairBidFormID
where 
subselRU.RepairBidID = @RepairBidID and subselRU.RepairBidSID = @RepairBidSID
group by
rb.BidCompany
,rih.RprEstCompletionDays
,subselRU.sumofamt
,rb.BidDocID
,rb.BidDocSID
,rb.AssetTaskID
,rb.AssetTaskSID
,rb.RepairBidID
,rb.RepairBidSID
,rb.StatusID
GO




 

Open in new window

0
 
LVL 5

Author Closing Comment

by:knowlton
ID: 39490681
Thank you.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question