4

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

closed as off-topic by LowlyDBA, John Eisbrener, Marcello Miorelli, MDCCL, McNets Sep 21 at 20:17

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, John Eisbrener, Marcello Miorelli, MDCCL, McNets
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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