[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