CREATE FUNCTION dbo.Event_Total_Data(@Member_Id
VARCHAR(30),@Total_Year int,@Quarter int,@Tyear int)
RETURNS int
AS
BEGIN
DECLARE @Point_Apply int
DECLARE @Plus_Minus int
DECLARE @Total_Point int
DECLARE @SQL nvarchar(300)
SET @Total_Point = 0
SET @SQL = ''
DECLARE Point_Cur CURSOR FOR
IF @Quarter = '' AND @Tyear = ''
SELECT Point_Apply,Plus_Minus FROM Event_Point_Data Where
Member_id=@Member_id And
Left(Wdate,4)=@Total_Year
ELSE IF @Quarter !='' AND @Tyear = ''
IF @Quarter = 1
SELECT Point_Apply,Plus_Minus FROM Event_Point_Data Where
Member_id=@Member_id And Left(Wdate,4)=@Total_Year And
(Substring(Wdate,6,2) > 1 And Substring(Wdate,6,2) < 4)
IF @Quarter = 2
SELECT Point_Apply,Plus_Minus FROM Event_Point_Data Where
Member_id=@Member_id And Left(Wdate,4)=@Total_Year And
(Substring(Wdate,6,2) > 3 And Substring(Wdate,6,2) < 7)
IF @Quarter = 3
SELECT Point_Apply,Plus_Minus FROM Event_Point_Data Where
Member_id=@Member_id And Left(Wdate,4)=@Total_Year And
(Substring(Wdate,6,2) > 6 And Substring(Wdate,6,2) <
10)
IF @Quarter = 4
SELECT Point_Apply,Plus_Minus FROM Event_Point_Data Where
Member_id=@Member_id And Left(Wdate,4)=@Total_Year And
(Substring(Wdate,6,2) > 9)
ELSE
SELECT Point_Apply,Plus_Minus FROM Event_Point_Data Where
Member_id=@Member_id And Left(Wdate,4)=@Total_Year And
Substring(Wdate,6,2)=@Tyear
OPEN Point_Cur
FETCH NEXT FROM Point_Cur INTO @Point_Apply,@Plus_Minus
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Plus_Minus = 1
SET @Total_Point = @Total_Point+@Point_Apply
IF @Plus_Minus = 0
SET @Total_Point = @Total_Point-@Point_Apply
FETCH NEXT FROM Point_Cur INTO @Point_Apply,@Plus_Minus
END
CLOSE Point_Cur
DEALLOCATE Point_Cur
RETURN(@Total_Point)
END
커서에서 동적쿼리가 먹는지요
Set @SQL = 'select '
Set @SQL = SQL + 'and '
exec (@SQL)
이런식으로요
IF @Quarter = '' AND @Tyear = ''
현재 이프문에서 에러가 나는데 왜그럴까요?
서버: 메시지 156, 수준 15, 상태 1, 프로시저 Event_Total_Data, 줄
13
'IF' 키워드 근처의 구문이 잘못되었습니다.
|