多了个endALTER FUNCTION ufn_GetCPCourseCount(@Email varchar(100))
RETURNS @RS TABLE(s int) AS
BEGIN
declare @functionID int
declare @positionID int
declare @courseCount int
declare @isMA int
select @functionID = CPFunctionID,@positionID=CPPositionLevelID,@isMA=ISNull(IsMA,0) from CPStaff where email = @Email if @isMA=1
begin
INSERT INTO @RS
select CPCourseID
from
(
SELECT
dbo.CPCourse.CPCourseID,
dbo.CPCourse.CourseNameEn,
dbo.CPCourse.CourseNameCn
FROM dbo.CPCourse INNER JOIN
dbo.CP_Course_Function ON dbo.CPCourse.CPCourseID = dbo.CP_Course_Function.CPCourseID INNER JOIN
dbo.CPFunction ON dbo.CP_Course_Function.CPFunctionID = dbo.CPFunction.CPFunctionID
WHERE dbo.CP_Course_Function.CPFunctionID = @functionID
UNION -- Get M&A course
SELECT
dbo.CPCourse.CPCourseID,
dbo.CPCourse.CourseNameEn,
dbo.CPCourse.CourseNameCn
FROM dbo.CPCourse INNER JOIN
dbo.CP_Course_PositionLevel ON dbo.CPCourse.CPCourseID = dbo.CP_Course_PositionLevel.CPCourseID INNER JOIN
dbo.CPPositionLevel ON dbo.CP_Course_PositionLevel.CPPositionLevelID = dbo.CPPositionLevel.CPPositionLevelID
WHERE dbo.CP_Course_PositionLevel.CPPositionLevelID = @positionID
UNION
SELECT CPCourseID, CourseNameEn, CourseNameCn
FROM dbo.CPCourse
WHERE (IsMA = 1)
) a
end
else
INSERT INTO @RS
select CPCourseID
from
(
SELECT
dbo.CPCourse.CPCourseID,
dbo.CPCourse.CourseNameEn,
dbo.CPCourse.CourseNameCn
FROM dbo.CPCourse INNER JOIN
dbo.CP_Course_Function ON dbo.CPCourse.CPCourseID = dbo.CP_Course_Function.CPCourseID INNER JOIN
dbo.CPFunction ON dbo.CP_Course_Function.CPFunctionID = dbo.CPFunction.CPFunctionID
WHERE dbo.CP_Course_Function.CPFunctionID = @functionID
UNION
SELECT
dbo.CPCourse.CPCourseID,
dbo.CPCourse.CourseNameEn,
dbo.CPCourse.CourseNameCn
FROM dbo.CPCourse INNER JOIN
dbo.CP_Course_PositionLevel ON dbo.CPCourse.CPCourseID = dbo.CP_Course_PositionLevel.CPCourseID INNER JOIN
dbo.CPPositionLevel ON dbo.CP_Course_PositionLevel.CPPositionLevelID = dbo.CPPositionLevel.CPPositionLevelID
WHERE dbo.CP_Course_PositionLevel.CPPositionLevelID = @positionID
) a
----end --这行去掉return
END
RETURNS @RS TABLE(s int) AS
BEGIN
declare @functionID int
declare @positionID int
declare @courseCount int
declare @isMA int
select @functionID = CPFunctionID,@positionID=CPPositionLevelID,@isMA=ISNull(IsMA,0) from CPStaff where email = @Email if @isMA=1
begin
INSERT INTO @RS
select CPCourseID
from
(
SELECT
dbo.CPCourse.CPCourseID,
dbo.CPCourse.CourseNameEn,
dbo.CPCourse.CourseNameCn
FROM dbo.CPCourse INNER JOIN
dbo.CP_Course_Function ON dbo.CPCourse.CPCourseID = dbo.CP_Course_Function.CPCourseID INNER JOIN
dbo.CPFunction ON dbo.CP_Course_Function.CPFunctionID = dbo.CPFunction.CPFunctionID
WHERE dbo.CP_Course_Function.CPFunctionID = @functionID
UNION -- Get M&A course
SELECT
dbo.CPCourse.CPCourseID,
dbo.CPCourse.CourseNameEn,
dbo.CPCourse.CourseNameCn
FROM dbo.CPCourse INNER JOIN
dbo.CP_Course_PositionLevel ON dbo.CPCourse.CPCourseID = dbo.CP_Course_PositionLevel.CPCourseID INNER JOIN
dbo.CPPositionLevel ON dbo.CP_Course_PositionLevel.CPPositionLevelID = dbo.CPPositionLevel.CPPositionLevelID
WHERE dbo.CP_Course_PositionLevel.CPPositionLevelID = @positionID
UNION
SELECT CPCourseID, CourseNameEn, CourseNameCn
FROM dbo.CPCourse
WHERE (IsMA = 1)
) a
end
else
INSERT INTO @RS
select CPCourseID
from
(
SELECT
dbo.CPCourse.CPCourseID,
dbo.CPCourse.CourseNameEn,
dbo.CPCourse.CourseNameCn
FROM dbo.CPCourse INNER JOIN
dbo.CP_Course_Function ON dbo.CPCourse.CPCourseID = dbo.CP_Course_Function.CPCourseID INNER JOIN
dbo.CPFunction ON dbo.CP_Course_Function.CPFunctionID = dbo.CPFunction.CPFunctionID
WHERE dbo.CP_Course_Function.CPFunctionID = @functionID
UNION
SELECT
dbo.CPCourse.CPCourseID,
dbo.CPCourse.CourseNameEn,
dbo.CPCourse.CourseNameCn
FROM dbo.CPCourse INNER JOIN
dbo.CP_Course_PositionLevel ON dbo.CPCourse.CPCourseID = dbo.CP_Course_PositionLevel.CPCourseID INNER JOIN
dbo.CPPositionLevel ON dbo.CP_Course_PositionLevel.CPPositionLevelID = dbo.CPPositionLevel.CPPositionLevelID
WHERE dbo.CP_Course_PositionLevel.CPPositionLevelID = @positionID
) a
----end --这行去掉return
END
end return
END --------------最后多了个 end
) a return
END