Post reply

Combine multiple stored procedures to create one report using cursor.

  • February 22, 2019 at 9:39 pm

    #379451

    What I'm trying to achieve should look like this and my stored procedure should accept a single argument – the name of a College, it will need to find all Colleges that match the name (including partial match). I need to repeat for all matching colleges that have for example the string 'new york' in it (that is where the cursor is important). Here is the link to the data dictionary: Data Dictionary

    Here is what I have currently: 
    use college
    go
    drop procedure if exists sp_25Report
    go

    CREATE PROCEDURE sp_25Report (
    @UNITID float /*the ID of the institution*/
    )
    AS
    BEGIN

    DECLARE @INSTNM VARCHAR(200) /*The institution name*/
    DECLARE @Query NVARCHAR(500)
    DECLARE @CUML_DEBT_P25 float  /*cumulative debt at 25%*/
    DECLARE @CUML_DEBT_N float /*the cumulative debt of N*/

    DECLARE @AVG25 float  /*  at 25 percent debt  */
    DECLARE @AVGN float   /*the average of N*/
    DECLARE @SUMN float  /* the sum of the debt N  */
    DECLARE @WAVG float /* the weighted average */

    SET @AVG25 = (select AVG(CUML_DEBT_P25) from Debt) /*Calculate the average at 25*/
    SET @AVGN = (select AVG(CUML_DEBT_N) from Debt) /*Calculate the average of N*/
    SET @WAVG = (select SUM(CUML_DEBT_P25*CUML_DEBT_N)/SUM(CUML_DEBT_N) from Debt) /*Calculate the weighted average*/

    select 'Cumulative debt at 25%' as Satistic, /*Select statement to output*/
    CUML_DEBT_P25 as 'Institution Value',
    @AVG25 as 'Average Value',
    @WAVG as 'Weighted Average' from Debt
    where UNITID = @UNITID /*stored procedure will use ID as input.
    Will change this to instnm to get partial match for string*/
    END
    GO
    exec sp_25Report 100654

    GO

    Here is what I'm trying to do and combine into one report based on the string or in this case the ID even with the ID having a partial match.
    use college
    go
    exec sp_90Report 100654
    exec sp_75Report 100654
    exec sp_25Report 100654
    exec sp_10Report 100654

  • February 22, 2019 at 10:39 pm

    #2022730

    Please post the table definitions for the relevant tables. What you're doing sounds way harder than this should be.

  • February 23, 2019 at 8:52 am

    #2022739

    Here's the link to my assignment: Assignment if this helps and here is the data dictionary: Data Dictionary. There is only one table "Debt" in the database "college" and I also added comments to my post above for definitions. Part of this assignment is that I have to use a cursor to do this. (Note this is my first time doing this as I only have been introduced to T-SQL about 2 weeks ago.)

  • February 27, 2019 at 5:00 pm

    #2023148

    Oh for crying out loud. Why do professors teach you how to use cursors, but not in the proper place? I'd challenge the professor and ask why it can't be done with a simple query. Don't get me wrong, cursors have their place. I just don't think this is it

  • February 27, 2019 at 7:04 pm

    #2023150

    I figured it out and used 4 separate functions to do this and then create a main stored procedure. If you'd like me to post the solution I can; this was really complicated for what I was trying to do....

  • February 27, 2019 at 8:08 pm

    #2023155

    Sure. Post the CREATE TABLE scripts too. Should be enlightening.

    Then we can all see if you really needed a cursor to accomplish the task.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply