3

This is my first time doing this... For my assignment, I need to combine multiple stored procedures into one big report using a cursor. There is only one table Debt from the database college. Here is a link to the data dictionary: Data Dictionary

The output for my report is supposed to output these values for a college name or even a partial match to a string. output for report

Currently, this is one of the 4 stored procedures that I have to output at 10%, 25%, 75%, and 90%. I'm trying to combine 10%, 25%, 75%, and 90% into one report when I execute the combined stored procedure. Also, when searching for a string in the combined stored procedure it will return any similar values. For example, the string 'ala' returns any strings in the format specified and in this case, it would probably return all colleges relating to Alamaba.

USE [college]
GO
/****** Object:  StoredProcedure [dbo].[sp_10Report]    Script Date: 
2/23/2019 1:41:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_10Report] (
@UNITID float
)
AS
BEGIN


DECLARE @INSTNM VARCHAR(200)
DECLARE @Query NVARCHAR(500)
DECLARE @CUML_DEBT_P10 float
DECLARE @CUML_DEBT_N float

DECLARE @AVG10 float 
DECLARE @AVGN float
DECLARE @SUMN float
DECLARE @WAVG float

SET @AVG10 = (select  AVG(CUML_DEBT_P10) from Debt)
SET @AVGN = (select AVG(CUML_DEBT_N) from Debt)
SET @WAVG = (select SUM(CUML_DEBT_P10*CUML_DEBT_N)/SUM(CUML_DEBT_N) from Debt)

select 'Cumulative debt at 10%' as Satistic, 
CUML_DEBT_P10 as 'Institution Value', 
@AVG10 as 'Average Value', 
@WAVG as 'Weighted Average' from Debt
where UNITID = @UNITID

END
GO

Here are the rows that are important. Rows that are important

  • Welcome to StackExchange! Your post is well done, easy to read, and a good example of real-world tasks that utilize less-than-perfect methods. One feedback is some people avoid links for data out of security concerns (turns out the file downloads empty for me though I can see it on my phone) so it might be helpful to pull a few of the rows into the question along with the table definition – clifton_h Feb 24 at 1:27
  • Thanks, it's an excel file that can be imported into the database. My variables should be self-explanatory but I will add the table definitions to which rows are important. The excel file will only give you one table with about 7800 rows in the database. – Andrew Polemeni Feb 24 at 15:12
0

Instead of combining 4 procedures, why not create 4 functions and have one procedure call all of them and give you the desired result?

CREATE FUNCTION Report10 (@UNITID float)

RETURNS @Report TABLE
(
Statistic nvarchar(50),
InstitutionValue float,
AverageValue float,
WeightedAverage float
)

AS

BEGIN

DECLARE @Temp AS TABLE (
Statistic nvarchar(50),
InstitutionValue float,
AverageValue float,
WeightedAverage float
)

INSERT INTO @Temp
SELECT  'Cumulative debt at 10%' AS [Statistic],
        AVG(M.CUML_DEBT_P10), 
        (SELECT AVG(CUML_DEBT_N)FROM MERGED2013_PP$), 
        (SELECT (SUM(CUML_DEBT_P10*CUML_DEBT_N)/SUM(CUML_DEBT_N))FROM MERGED2013_PP$)
FROM MERGED2013_PP$ AS M
WHERE UNITID = @UNITID

INSERT INTO @Report
SELECT T.Statistic, T.InstitutionValue, T.AverageValue, T.WeightedAverage
FROM @Temp AS T

RETURN

END
GO

You can create one for each, 10%, 25%, 75% and 90%. Also I am sure the "AverageValue" column in my solution is calculated wrongly, but you can fix that since I don't know what it's supposed to be, I simply used your calculations.

This procedure would call them all:

CREATE PROCEDURE Report(@UNITID float) 
AS
BEGIN

    SELECT * FROM Report10(@UNITID)

    UNION 

    SELECT * FROM Report25(@UNITID)

    UNION

    SELECT * FROM Report75(@UNITID)

    UNION

    SELECT * FROM Report90(@UNITID)

END
GO

And this would give you the following output:

enter image description here

This gives you the desired output except for the [AverageValue] column which you can change how it's calculated.

This might not be the most elegant solution, but it works. Hopefully this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.