[thelist] MSSQL Stored Proc problem
Steve Lewis
slewis at macrovista.net
Fri Jan 3 16:09:05 CST 2003
rudy wrote:
> have you tested the query in query analyzer or enterprise manager?
btw, here is a copy of the actual proc in one of it's forms
(ResponsesByQ is a view that SELECTs [...] FROM Questions join Responses
on QuestionID = QuestionID) ... the DB design is inherited.
ALTER PROC CapacityTrustOverview
@BuildingList varchar(50),
@BreakingList varchar(50),
@BuildingCountReq int,
@BreakingCountReq int,
@FormID int,
@UserGroupID int
AS
DECLARE @UserGroupFormID int
SELECT @UserGroupFormID = UserGroupFormID
FROM UserGroup_Forms
WHERE FormID = @FormID
AND UserGroupID = @UserGroupID
SELECT Round(Avg(Cast(Average AS decimal(5,2))),2) as Average,
Round(Avg(Cast(Building AS decimal(5,2))),2) as Building,
Round(Avg(Cast(Breaking AS decimal(5,2))),2) as Breaking,
COUNT(Distinct UserID) AS Num
FROM
(
(
SELECT Abs(Response - 6) AS Average, NULL AS Building,
Response AS Breaking, ResponsesByQ.UserID
FROM ResponsesByQ
INNER JOIN
(
SELECT COUNT(*) AS ResponseCount, UserID
FROM ResponsesByQ
WHERE QuestionNumber IN (@BreakingList)
AND UserGroupFormID = @UserGroupFormID
GROUP BY UserID
) RCount ON ResponsesByQ.UserID = RCount.UserID
WHERE QuestionNumber IN (@BreakingList)
AND ResponseCount >= @BreakingCountReq
)
UNION ALL
(
SELECT Response, Response, NULL, ResponsesByQ.UserID
FROM ResponsesByQ
INNER JOIN
(
SELECT COUNT(*) AS ResponseCount, UserID
FROM ResponsesByQ
WHERE QuestionNumber IN (@BuildingList)
AND UserGroupFormID = @UserGroupFormID
GROUP BY UserID
) RCount ON ResponsesByQ.UserID = RCount.UserID
WHERE QuestionNumber IN (@BuildingList)
AND ResponseCount >= @BuildingCountReq
)
) rPrime
GO
--Steve
More information about the thelist
mailing list