none
Populate four worksheets with four SQL Temp table select statements from a single StoredProc RRS feed

  • Question

  • Excel calls the stored procedure successfully, but loads only one result in the first worksheet.

    {CALL db1.dbo.usp_Fees (?)}

    The storedproc builds 4 temp tables and finishes with these four selects (which need to populate 4 worksheets): 

    select * from #TempAllItems;

    Select * from #TempFeeDetails;

    Select * from #TempFeeCategories;

    Select * from #TempTotalFees;

    I can make 4 storedProcs, one for each result, but I would rather run once and load the 4 selects into 4 worksheets.

    I would like Sheet1 to load the TotalFees, Sheet2 to load the FeeCategories, Sheet3 to load FeeDetails, and Sheet4 to load AllItems. Each worksheet shows the details of the prior worksheet.

    Thank you

    Monday, April 18, 2016 10:48 PM

Answers

  • Hi, jesup

    • Excel calls the stored procedure successfully, but loads only one result in the first worksheet.
    • how did you find all procedures run successfully together. did you try to debug?
    • it is working fine when you call it individually but you want to execute them together.
    • did you call all procedures and assign a individual worksheet to them in code?
    • can you show us your code?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by jesup Tuesday, April 19, 2016 3:46 PM
    Tuesday, April 19, 2016 6:55 AM
    Moderator

All replies

  • Hi, jesup

    • Excel calls the stored procedure successfully, but loads only one result in the first worksheet.
    • how did you find all procedures run successfully together. did you try to debug?
    • it is working fine when you call it individually but you want to execute them together.
    • did you call all procedures and assign a individual worksheet to them in code?
    • can you show us your code?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by jesup Tuesday, April 19, 2016 3:46 PM
    Tuesday, April 19, 2016 6:55 AM
    Moderator
  • Hi jesup,

    I have not seen your code so I am unsure how you are doing it but you have mentioned that you are able to do it individually.

    so here I would like to suggest you that you can place the code on individual worksheet. so that individual procedure will call for particular worksheet and data will loaded into that.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 19, 2016 7:07 AM
    Moderator
  • My code is too long, and it ends with the 4 consecutive select statements.

    SQL Server Management Server executes the single stored procedure which returns the 4 select statements as they execute.  The Excel dqy file only returns the first select statement. 

    My wish is to assign each of the 4 select statements to a recordset that can be assign to an individual Excel worksheet.  You suggestion to do this individually works, and this is what I will do. 

    I could also Union the 4 select statements and add a key column for an array identifier, then return all 4 selects together and parse them out into 4 array keys - assign a recordset to each unique array item.  Seems like a lot of extra manipulations to use one stored procedure with 4 outputs. 

    Since the #temp tables are open (I believe they are), Excel should still have access to the named temp tables for assignment to individual worksheets.  Probably not.

    Thanks for your suggestion, which I will use.

    Tuesday, April 19, 2016 3:46 PM