I'm stumped here. I'm sure I'm forgetting something basic but... I've got a custom module that works on my local machine. It works on my production server too except the DAL keeps bringing back empty objects. I know the table it uses has been copied over because I can use the host SQL page to bring it up. But yet the DAL on the server keeps coming back with an empty object when I run the following Stored Procedure. I don't know why? I used CodeSmith's "ScriptTable" to transfer the Table to my server with the data. I've used it before and haven't had any problems. I'm stumped on this one though. My SP is below and thanks to anyone that can point out my mistake:
CREATE PROCEDURE dbo.GetRynoStatAverages
@StatType int,
@Year int,
@CommissionCode int,
@AFSC char(10)
AS
If @AFSC='0' SET @AFSC=null
DECLARE @RecCount int
DECLARE @NumPriorE decimal(18,0)
/********** HAD to make this a decimal above because it's a SQL quirk *********
*******If you have A/B where A and B are both integers, the result will be 0 ******/
DECLARE @PctE decimal(18,0)
DECLARE @NumAAS decimal(18,0)
DECLARE @PctAAS decimal(18,0)
DECLARE @NumAAScc decimal(18,0)
DECLARE @PctAAScc decimal(18,0)
DECLARE @NumROTCcc decimal(18,0)
DECLARE @PctROTCcc decimal(18,0)
DECLARE @NumApplyENJPT decimal(18,0)
DECLARE @PctApplyENJPT decimal(18,0)
DECLARE @NumOnWaiver decimal(18,0)
DECLARE @PctOnWaiver decimal(18,0)
DECLARE @NumOTSasE decimal(18,0)
DECLARE @PctOTSasE decimal(18,0)
DECLARE @NumPriorGuardE decimal(18,0)
DECLARE @PctPriorGuardE decimal(18,0)
DECLARE @NumMale decimal(18,0)
DECLARE @PctMale decimal(18,0)
DECLARE @NumTechDegree decimal(18,0)
DECLARE @PctTechDegree decimal(18,0)
DECLARE @NumRated decimal(18,0)
DECLARE @PctRated decimal(18,0)
DECLARE @NumPPL decimal(18,0)
DECLARE @PctPPL decimal(18,0)
DECLARE @NumInstrument decimal(18,0)
DECLARE @PctInstrument decimal(18,0)
DECLARE @NumCommercial decimal(18,0)
DECLARE @PctCommercial decimal(18,0)
DECLARE @NumCFI decimal(18,0)
DECLARE @PctCFI decimal(18,0)
DECLARE @NumCFII decimal(18,0)
DECLARE @PctCFII decimal(18,0)
DECLARE @NumApplyPilot decimal(18,0)
DECLARE @PctApplyPilot decimal(18,0)
DECLARE @NumTrainingGrad decimal(18,0)
DECLARE @PctTrainingGrad decimal(18,0)
DECLARE @NumFCIqualified decimal(18,0)
DECLARE @PctFCIqualified decimal(18,0)
DECLARE @NumOnScholarship decimal(18,0)
DECLARE @PctOnScholarship decimal(18,0)
IF (@Year<>0)
BEGIN
SELECT @RecCount = (SELECt Count(*) FROM RynoStats
WHERE [STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
IF (@RecCount = 0)
BEGIN
RETURN @RecCount
END
SELECT @NumPriorE = (SELECT COUNT(*) FROM RynoStats
WHERE [PriorE] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctE = @NumPriorE / @RecCount * 100
SELECT @NumAAS = (SELECT COUNT(*) FROM RynoStats
WHERE [AASmember] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctAAS = @NumAAS / @RecCount * 100
SELECT @NumAAScc = (SELECT COUNT(*) FROM RynoStats
WHERE [AAScc] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctAAScc = @NumAAScc / @RecCount * 100
SELECT @NumROTCcc = (SELECT COUNT(*) FROM RynoStats
WHERE [ROTCcc] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctROTCcc = @NumROTCcc / @RecCount * 100
SELECT @NumApplyENJPT = (SELECT COUNT(*) FROM RynoStats
WHERE [ApplyENJPT] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctApplyENJPT = @NumApplyENJPT / @RecCount * 100
SELECT @NumOnWaiver = (SELECT COUNT(*) FROM RynoStats
WHERE [WaiverType] <> 0 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctOnWaiver = @NumOnWaiver / @RecCount * 100
SELECT @NumOTSasE = (SELECT COUNT(*) FROM RynoStats
WHERE [OTSasE] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctOTSasE = @NumOTSasE / @RecCount * 100
SELECT @NumPriorGuardE = (SELECT COUNT(*) FROM RynoStats
WHERE [PriorGuardE] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctPriorGuardE = @NumPriorGuardE / @RecCount * 100
SELECT @NumMale = (SELECT COUNT(*) FROM RynoStats
WHERE [MaleNotFemale] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctMale = @NumMale / @RecCount * 100
SELECT @NumTechDegree = (SELECT COUNT(*) FROM RynoStats
WHERE [TechnicalDegree] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctTechDegree = @NumTechDegree / @RecCount * 100
SELECT @NumRated = (SELECT COUNT(*) FROM RynoStats
WHERE [Rated] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctRated = @NumRated / @RecCount * 100
SELECT @NumPPL= (SELECT COUNT(*) FROM RynoStats
WHERE [PPL] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctPPL = @NumPPL / @RecCount * 100
SELECT @NumInstrument = (SELECT COUNT(*) FROM RynoStats
WHERE [Instrument] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctInstrument = @NumInstrument / @RecCount * 100
SELECT @NumCommercial = (SELECT COUNT(*) FROM RynoStats
WHERE [Commercial] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctCommercial = @NumCommercial / @RecCount * 100
SELECT @NumCFI = (SELECT COUNT(*) FROM RynoStats
WHERE [CFI] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctCFI = @NumCFI / @RecCount * 100
SELECT @NumCFII = (SELECT COUNT(*) FROM RynoStats
WHERE [CFII] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctCFII = @NumCFII / @RecCount * 100
SELECT @NumApplyPilot = (SELECT COUNT(*) FROM RynoStats
WHERE [ApplyPilot] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctApplyPilot = @NumApplyPilot / @RecCount * 100
SELECT @NumtrainingGrad = (SELECT COUNT(*) FROM RynoStats
WHERE [traininggrad] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @Pcttraininggrad = @Numtraininggrad / @RecCount * 100
SELECT @NumFCIqualified = (SELECT COUNT(*) FROM RynoStats
WHERE [FCIqualified] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctFCIqualified = @NumFCIqualified / @RecCount * 100
SELECT @Numonscholarship = (SELECT COUNT(*) FROM RynoStats
WHERE [scholarship] <> 0 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR)
SELECT @PctOnScholarship = @NumOnscholarship / @RecCount * 100
SELECT
AVG(GPA) AS GPA,
AVG(PCSM) AS PCSM,
AVG(PFT) AS PFT,
AVG(OM) AS OM,
AVG(RSS) AS RSS,
@PctE AS PRIORE,
@pctAAS as AASmember,
@pctAAScc as AAScc,
@pctROTCcc as ROTCcc,
AVG(CampScore) AS campscore,
AVG(AFOQT_Pilot) as AFOQT_PILOT,
AVG(AFOQT_Nav) AS AFOQT_NAV,
AVG(AFOQT_Quant) AS AFOQT_QUANT,
AVG(AFOQT_Verbal) AS AFOQT_VERBAL,
AVG(AFOQT_AA) AS AFOQT_AA,
@pctApplyENJPT as ApplyENJPT,
AVG(CCRankYou) AS CCRankYou,
AVG(CCRankTotal) AS CCRankTotal,
AVG(FlyHours) AS FLYHOURS,
@pctOnWaiver as OnWaiver,
AVG(CommYear) AS CommYear,
@pctOTSasE as OTSasE,
@pctPriorGuardE as PriorGuardE,
AVG(Age) AS Age,
@pctmale as MaleNotFemale,
@pctTechDegree as TechnicalDegree,
@pctRated as Rated,
@pctPPL as PPL,
@pctInstrument as Instrument,
@pctCommercial as Commercial,
@pctCFI as CFI,
@pctCFII as CFII,
@pctApplyPilot as ApplyPilot,
@pctTrainingGrad as TrainingGrad,
@pctFCIqualified as FCIqualified,
COUNT(*) AS RecCount,
MAX(gpa) as gpah,
MIN(gpa) as gpal,
MAX(pcsm) as pcsmh,
MIN(pcsm) as pcsml,
MAX(pft) as pftH,
MIN(pft) as pftL,
MAX(om) as omh,
MIN(om) as oml,
MAX(rss) as rssh,
MIN(rss) as rssl,
MAX(afoqt_pilot) as afoqt_piloth,
MIN(afoqt_pilot) as afoqt_pilotl,
MAX(afoqt_nav) as afoqt_navh,
MIN(afoqt_nav) as afoqt_navl,
MAX(afoqt_quant) as afoqt_quanth,
MIN(afoqt_quant) as afoqt_quantl,
MAX(afoqt_verbal) as afoqt_verbalh,
MIN(afoqt_verbal) as afoqt_verball,
MAX(afoqt_aa) as afoqt_aah,
MIN(afoqt_aa) as afoqt_aal,
MAX(ccrankyou) as ccrankyouH,
MIN(ccrankyou) as ccrankyouL,
MAX(flyhours) as flyhoursh,
MIN(flyhours) as flyhoursl,
MAX(age) as ageh,
MIN(age) as agel,
@PctOnScholarship as OnScholarship
FROM RynoStats
WHERE
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC AND
[COMMYEAR] = @YEAR
GROUP BY
[COMMYEAR]
ORDER BY
[COMMYEAR]
END
IF (@Year=0)
BEGIN
SELECT @RecCount = (SELECt Count(*) FROM RynoStats
WHERE [STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC)
IF (@RecCount = 0)
BEGIN
RETURN @RecCount
END
SELECT @NumPriorE = (SELECT COUNT(*) FROM RynoStats
WHERE [PriorE] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctE = @NumPriorE / @RecCount * 100
SELECT @NumAAS = (SELECT COUNT(*) FROM RynoStats
WHERE [AASmember] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctAAS = @NumAAS / @RecCount * 100
SELECT @NumAAScc = (SELECT COUNT(*) FROM RynoStats
WHERE [AAScc] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctAAScc = @NumAAScc / @RecCount * 100
SELECT @NumROTCcc = (SELECT COUNT(*) FROM RynoStats
WHERE [ROTCcc] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctROTCcc = @NumROTCcc / @RecCount * 100
SELECT @NumApplyENJPT = (SELECT COUNT(*) FROM RynoStats
WHERE [ApplyENJPT] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC)
SELECT @PctApplyENJPT = @NumApplyENJPT / @RecCount * 100
SELECT @NumOnWaiver = (SELECT COUNT(*) FROM RynoStats
WHERE [WaiverType] <> 0 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctOnWaiver = @NumOnWaiver / @RecCount * 100
SELECT @NumOTSasE = (SELECT COUNT(*) FROM RynoStats
WHERE [OTSasE] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctOTSasE = @NumOTSasE / @RecCount * 100
SELECT @NumPriorGuardE = (SELECT COUNT(*) FROM RynoStats
WHERE [PriorGuardE] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC)
SELECT @PctPriorGuardE = @NumPriorGuardE / @RecCount * 100
SELECT @NumMale = (SELECT COUNT(*) FROM RynoStats
WHERE [MaleNotFemale] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC)
SELECT @PctMale = @NumMale / @RecCount * 100
SELECT @NumTechDegree = (SELECT COUNT(*) FROM RynoStats
WHERE [TechnicalDegree] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctTechDegree = @NumTechDegree / @RecCount * 100
SELECT @NumRated = (SELECT COUNT(*) FROM RynoStats
WHERE [Rated] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctRated = @NumRated / @RecCount * 100
SELECT @NumPPL= (SELECT COUNT(*) FROM RynoStats
WHERE [PPL] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC)
SELECT @PctPPL = @NumPPL / @RecCount * 100
SELECT @NumInstrument = (SELECT COUNT(*) FROM RynoStats
WHERE [Instrument] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctInstrument = @NumInstrument / @RecCount * 100
SELECT @NumCommercial = (SELECT COUNT(*) FROM RynoStats
WHERE [Commercial] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctCommercial = @NumCommercial / @RecCount * 100
SELECT @NumCFI = (SELECT COUNT(*) FROM RynoStats
WHERE [CFI] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC)
SELECT @PctCFI = @NumCFI / @RecCount * 100
SELECT @NumCFII = (SELECT COUNT(*) FROM RynoStats
WHERE [CFII] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctCFII = @NumCFII / @RecCount * 100
SELECT @NumApplyPilot = (SELECT COUNT(*) FROM RynoStats
WHERE [ApplyPilot] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctApplyPilot = @NumApplyPilot / @RecCount * 100
SELECT @NumtrainingGrad = (SELECT COUNT(*) FROM RynoStats
WHERE [traininggrad] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @Pcttraininggrad = @Numtraininggrad / @RecCount * 100
SELECT @NumFCIqualified = (SELECT COUNT(*) FROM RynoStats
WHERE [FCIqualified] = 1 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC)
SELECT @PctFCIqualified = @NumFCIqualified / @RecCount * 100
SELECT @Numonscholarship = (SELECT COUNT(*) FROM RynoStats
WHERE [scholarship] <> 0 AND
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC )
SELECT @PctOnScholarship = @NumOnscholarship / @RecCount * 100
SELECT
AVG(GPA) AS GPA,
AVG(PCSM) AS PCSM,
AVG(PFT) AS PFT,
AVG(OM) AS OM,
AVG(RSS) AS RSS,
@PctE AS PRIORE,
@pctAAS as AASmember,
@pctAAScc as AAScc,
@pctROTCcc as ROTCcc,
AVG(CampScore) AS campscore,
AVG(AFOQT_Pilot) as AFOQT_PILOT,
AVG(AFOQT_Nav) AS AFOQT_NAV,
AVG(AFOQT_Quant) AS AFOQT_QUANT,
AVG(AFOQT_Verbal) AS AFOQT_VERBAL,
AVG(AFOQT_AA) AS AFOQT_AA,
@pctApplyENJPT as ApplyENJPT,
AVG(CCRankYou) AS CCRankYou,
AVG(CCRankTotal) AS CCRankTotal,
AVG(FlyHours) AS FLYHOURS,
@pctOnWaiver as OnWaiver,
AVG(CommYear) AS CommYear,
@pctOTSasE as OTSasE,
@pctPriorGuardE as PriorGuardE,
AVG(Age) AS Age,
@pctmale as MaleNotFemale,
@pctTechDegree as TechnicalDegree,
@pctRated as Rated,
@pctPPL as PPL,
@pctInstrument as Instrument,
@pctCommercial as Commercial,
@pctCFI as CFI,
@pctCFII as CFII,
@pctApplyPilot as ApplyPilot,
@pctTrainingGrad as TrainingGrad,
@pctFCIqualified as FCIqualified,
COUNT(*) AS RecCount,
MAX(gpa) as gpah,
MIN(gpa) as gpal,
MAX(pcsm) as pcsmh,
MIN(pcsm) as pcsml,
MAX(pft) as pftH,
MIN(pft) as pftL,
MAX(om) as omh,
MIN(om) as oml,
MAX(rss) as rssh,
MIN(rss) as rssl,
MAX(afoqt_pilot) as afoqt_piloth,
MIN(afoqt_pilot) as afoqt_pilotl,
MAX(afoqt_nav) as afoqt_navh,
MIN(afoqt_nav) as afoqt_navl,
MAX(afoqt_quant) as afoqt_quanth,
MIN(afoqt_quant) as afoqt_quantl,
MAX(afoqt_verbal) as afoqt_verbalh,
MIN(afoqt_verbal) as afoqt_verball,
MAX(afoqt_aa) as afoqt_aah,
MIN(afoqt_aa) as afoqt_aal,
MAX(ccrankyou) as ccrankyouH,
MIN(ccrankyou) as ccrankyouL,
MAX(flyhours) as flyhoursh,
MIN(flyhours) as flyhoursl,
MAX(age) as ageh,
MIN(age) as agel,
@PctOnScholarship as OnScholarship
FROM RynoStats
WHERE
[STATTYPE] = @StatType AND
[COMMISSIONCODE] = @CommissionCode AND
[AFSC] = @AFSC
END
GO
I'm too poor for anything other than the community version
|