如何测试SQL存储过程？

rosepark · 发表于 2009-3-5 11:52:14

查了一些资料，还是不明白存储过程怎样测试，摘了段存储过程，请大家指点下，我也能多学些测试方法。多谢

DELETE FROM HourlyScoringHistory_All
WHERE DayID = @DayId AND HourNum = @HourNum AND BatchID = @JobID;

DECLARE @UsersInLiveSegments TABLE
(
UserType varchar(1),
UserCount int
);

INSERT INTO @UsersInLiveSegments
SELECT
cosmosResult.UserType,
sum(cosmosResult.[Count])
FROM
KPI_SegIdUserTypeCountryAggregate cosmosResult INNER JOIN
Segment
ON cosmosResult.SegmentAuthoringId = Segment.SegmentAuthoringId
WHERE Segment.Live = 1
GROUP BY cosmosResult.UserType;

DECLARE @UsersInTestSegments TABLE
(
UserType varchar(1),
UserCount int
);

INSERT INTO @UsersInTestSegments
SELECT
cosmosResult.UserType,
sum(cosmosResult.[Count])
FROM
KPI_SegIdUserTypeCountryAggregate cosmosResult INNER JOIN
Segment
ON cosmosResult.SegmentAuthoringId = Segment.SegmentAuthoringId
WHERE Segment.Live = 0
GROUP BY cosmosResult.UserType;

INSERT INTO HourlyScoringHistory_All
(
DayID,
HourNum,
BatchID,
NumUsersInSegments,
NumUsersInLiveSegments,
NumUsersInTestSegments,
UserType
)
SELECT
@DayId,
@HourNum,
@JobID,
ISNULL(test.UserCount,0)+ISNULL(live.UserCount,0),
ISNULL(live.UserCount,0),
ISNULL(test.UserCount,0),
ISNULL(test.UserType,live.UserType)
FROM
@UsersInTestSegments test FULL OUTER JOIN @UsersInLiveSegments live
ON test.UserType = live.UserType;

[ 本帖最后由 rosepark 于 2009-3-26 11:17 编辑 ]