게시판 본문 COM / COM+ by VB 6.0 - Read Only

Case 부분에서 'AND 키워드 근처의 구문이 잘못되었습니다.' 라는 에러 메시지가 납니다. 왜 일까요? 동적 sql의 촉복과 저주에서 된다고 했는데;; 왜 안될까요?

여러모로 해보았는데 안되내요;;

★★아님 다른 방법이라도 잇으면 알려주세요. 왠만하면 동적은 안쓰고 페이지 해볼려고 하는데

다른 방법은 있을까요???

조건 대로 다 하드 코딩하는건 말고요.


CREATE PROCEDURE [knowledgeJump_list_usp]

@Contents_Code int ,
@Page_Size int ,
@Now_Page int ,
@Field varchar(20) ,
@Str varchar(30) ,
@Tot_Rec int OUTPUT ,
@Tot_Page int OUTPUT

AS
BEGIN
SET NOCOUNT ON

--BEGIN TRAN

Declare @SQL NVarChar(1000)
Set @SQL = 'SELECT Top '+ Convert(NvarChar(5),@Page_Size) +' k_j.K_Jump_Num, k_j.K_Jump_Sepa,
k_j.K_Jump_CateCode, k_j.K_Jump_CNum,
k_j.K_Jump_QNum, k_j.K_Jump_Title, k_j.K_Jump_WriterID,
k_j.K_Jump_WriteDate, k_j.K_Jump_Contents, k_j.K_Jump_ViewCNT,
k_c.K_Category_code, k_c.K_Category_Name, k_c.K_ParentCategory,
k_c_self.K_Category_Name AS smallCate_Name
FROM dbo.Know_Jump_TB k_j INNER JOIN
dbo.Know_Category k_c ON
k_j.K_Jump_CateCode = k_c.K_Category_code INNER JOIN
dbo.Know_Category k_c_self ON
k_c.K_ParentCategory = k_c_self.K_Category_code
WHERE (k_j.K_Jump_CNum = '+ Convert(NvarChar(10),@Contents_Code) +' ) AND (k_j.K_Jump_Sepa = ''1'')
AND k_j.K_Jump_Num NOT IN (SELECT TOP '+ Convert(NvarChar(10) , ( @Now_Page - 1 ) * @Page_Size ) +'
k_j.K_Jump_Num FROM Know_Jump_TB k_j ORDER BY k_j.K_Jump_Num DESC ) '
If @Str <> '' BEGIN
Set @SQL = @SQL + ' AND '+ Convert(nvarchar(20),@Field) +' LIKE ''%'+ Convert(nvarchar(50),@Str) +'%'' '
END
Set @SQL = @SQL + ' ORDER BY k_j.K_Jump_Num DESC '

-- EXEC SP_ExecuteSQL @SQL ,N'@tmp_Page_Size int , @tmp_Tot_Rec int OUTPUT , @tmp_Tot_Page int
OUTPUT' , @tmp_Page_Size = @Page_Size , @tmp_Tot_Rec = @Tot_Rec OUTPUT , @tmp_Tot_Page =
@Tot_Page OUTPUT
EXEC SP_ExecuteSQL @SQL

-- Declare @SQL NVarChar(500)
-- Set @SQL = 'SELECT @tmp_Tot_Rec = Count(*) , @tmp_Tot_Page = Ceiling ( Count(*) / @tmp_Page_Size )
FROM Know_Jump_TB'
-- EXEC SP_ExecuteSQL @SQL ,N'@tmp_Page_Size int , @tmp_Tot_Rec int OUTPUT , @tmp_Tot_Page int
OUTPUT' , @tmp_Page_Size = @Page_Size , @tmp_Tot_Rec = @Tot_Rec OUTPUT , @tmp_Tot_Page =
@Tot_Page OUTPUT

★★ 문제의 부분 ★★
Select @Tot_Rec = Count(*) FROM Know_Jump_TB k_j WHERE (k_j.K_Jump_CNum = @Contents_Code ) AND
(k_j.K_Jump_Sepa = 1)
If @Str <> '' BEGIN
AND ( CASE @Field
WHEN 'title' THEN k_j.K_Jump_Title
WHEN 'contents' THEN k_j.K_Jump_content
WHEN 'writer' THEN k_j.K_Jump_writer
END
LIKE '%@Str%' )
END



Set @Tot_Page = Ceiling( Convert(FLOAT, @Tot_Rec )/@Page_Size)

-- IF (@@ERROR<> 0) BEGIN
-- ROLLBACK TRAN
-- END
-- ELSE BEGIN
-- COMMIT TRAN
-- END

SET NOCOUNT OFF
END
GO