We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Create Stored Procedure which inserts values from multipule queries into table

R_Hos
R_Hos asked
on
Medium Priority
258 Views
Last Modified: 2008-01-09
I'm not as skilled with SQL as i would like to be, I need to insert the values from multipule queries into a new table.  I've done similar inserts with a single query but i'm just not getting this.  

I've included the code i have so far.

-- Drop this procedure if it already exist
IF object_id('SP_Create_AdHoc_Totals_Table') IS NOT NULL
      BEGIN
            DROP PROCEDURE SP_Create_AdHoc_Totals_Table
      END
GO

CREATE PROCEDURE SP_Create_AdHoc_Report_Table @GrantID INT = NULL
AS
      
-- Drop this table if it already exist
IF object_id('tbl_AdHoc_Totals') IS NOT NULL
      BEGIN
            DROP TABLE tbl_AdHoc_Totals
      END
GO

CREATE TABLE tbl_AdHoc_Totals
(
GrantID int,
OriginalTotal decimal NULL,
AdjustmentTotal decimal NULL,
ReimbursedTotal decimal NULL
)

INSERT tbl_AdHoc_Totals
      @GrantID,
      
      (SELECT SUM(lineItems.Total) AS OriginalTotal
      FROM LineItems
      WHERE GrantID = @GrantID),

      (SELECT SUM(Adjustment) AS AdjustmentTotal
      FROM lineitems LEFT JOIN lopapprove_adjustment_by_lineitemid ON lineitems.lineitemid = lopapprove_adjustment_by_lineitemid.lineitemid
      WHERE LineItems.GrantID = @GrantID),

      (SELECT SUM(Reimbursed) AS ReimbursedTotal
      FROM lineitems LEFT JOIN reimbursed_by_lineitemid ON lineitems.lineitemid = reimbursed_by_lineitemid.lineitemid
      WHERE LineItems.GrantID = @GrantID)

SELECT *
FROM tbl_AdHoc_Totals

GO
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Modify the Insert statement as

INSERT tbl_AdHoc_Totals
Select
@GrantID,

(SELECT SUM(lineItems.Total) AS OriginalTotal
     FROM LineItems
     WHERE GrantID = @GrantID),

(SELECT SUM(Adjustment) AS AdjustmentTotal
     FROM lineitems LEFT JOIN lopapprove_adjustment_by_lineitemid
     ON lineitems.lineitemid = opapprove_adjustment_by_lineitemid.lineitemid
     WHERE LineItems.GrantID = @GrantID),

(SELECT SUM(Reimbursed) AS ReimbursedTotal
     FROM lineitems LEFT JOIN reimbursed_by_lineitemid
     ON lineitems.lineitemid = reimbursed_by_lineitemid.lineitemid
     WHERE LineItems.GrantID = @GrantID)


NM

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
IF object_id('SP_Create_AdHoc_Totals_Table') IS NOT NULL
     BEGIN
          DROP PROCEDURE SP_Create_AdHoc_Totals_Table
     END
GO

CREATE PROCEDURE SP_Create_AdHoc_Report_Table @GrantID INT = NULL
AS
     

DECLARE  @tbl_AdHoc_Totals  TABLE(          --- replaced Table with table variables
GrantID int,
OriginalTotal decimal NULL,
AdjustmentTotal decimal NULL,
ReimbursedTotal decimal NULL
)

INSERT INTO @tbl_AdHoc_Totals               -- insert into -Into was missing earlier
SELECT    @GrantID,

    (SELECT SUM(lineItems.Total) FROM LineItems
    WHERE GrantID = @GrantID),

    (SELECT SUM(Adjustment)
    FROM lineitems LEFT JOIN lopapprove_adjustment_by_lineitemid ON lineitems.lineitemid = lopapprove_adjustment_by_lineitemid.lineitemid
    WHERE LineItems.GrantID = @GrantID),

    (SELECT SUM(Reimbursed) AS ReimbursedTotal
    FROM lineitems LEFT JOIN reimbursed_by_lineitemid ON lineitems.lineitemid = reimbursed_by_lineitemid.lineitemid
    WHERE LineItems.GrantID = @GrantID)

SELECT *
FROM @tbl_AdHoc_Totals

GO


--If only these much code is present theb you dont need the insert statements





CREATE PROCEDURE SP_Create_AdHoc_Report_Table @GrantID INT = NULL
AS

SELECT    @GrantID AS GrantID,

    (SELECT SUM(lineItems.Total) FROM LineItems
    WHERE GrantID = @GrantID)AS OriginalTotal,

    (SELECT SUM(Adjustment)
    FROM lineitems LEFT JOIN lopapprove_adjustment_by_lineitemid ON lineitems.lineitemid = lopapprove_adjustment_by_lineitemid.lineitemid
    WHERE LineItems.GrantID = @GrantID)AS AdjustmentTotal,

    (SELECT SUM(Reimbursed) AS ReimbursedTotal
    FROM lineitems LEFT JOIN reimbursed_by_lineitemid ON lineitems.lineitemid = reimbursed_by_lineitemid.lineitemid
    WHERE LineItems.GrantID = @GrantID)ReimbursedTotal

GO
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.