Tuesday, November 29, 2011

T-SLQ Stored Procedure Return Value

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE GetUserCourseCompletedPercent
@UserEmail nchar(100),
@CourseId int

AS
BEGIN

SET NOCOUNT ON;
DECLARE @TotalCount REAL;
DECLARE @CompletedCount REAL;
(select @TotalCount =
count(ua.id)
from
 [MSInvolveOD].[dbo].[msinvolveCourseModule] m
 join [MSInvolveOD].[dbo].[msinvolveCourse] c
 on m.courseid = c.id
 join [MSInvolveOD].[dbo].[msinvolveModuleActivity] ma
 on
 ma.moduleid = m.id
 join [MSInvolveOD].[dbo].[msinvolveUserActivity] ua
 on ma.id = ua.activityid
 where c.id = @CourseId
 and ua.userid = @UserEmail )


select @CompletedCount =
count(ma.id)
from
 [MSInvolveOD].[dbo].[msinvolveCourseModule] m
 join [MSInvolveOD].[dbo].[msinvolveCourse] c
 on m.courseid = c.id
 join [MSInvolveOD].[dbo].[msinvolveModuleActivity] ma
 on
 ma.moduleid = m.id
 where c.id = @CourseId
 RETURN ROUND (( (@TotalCount / @CompletedCount) * 100 ), 0)                                                              

END
GO

No comments:

Post a Comment