프로시져 동적 쿼리 - 검색어 쿼리

저장 프로시저

/*********************************************************************  

Name : [up_st_room_facilities_list]

Auth : 장정훈

Date : 2014-04-09  

Desc : 관리자 > 부대시설 목록

declare @totalCount bigint

exec up_st_room_facilities_list 'name', '', @totalCount output

print @totalCount

-----------------------------------------------------------------------------------------------------------------------------------------*/

alter proc [dbo].[up_st_room_facilities_list]

@searchType varchar(10),

@searchWord varchar(20),

@totalCount bigint output

as 

begin 

select @totalCount = count(*)

from tb_st_facilities_info with(nolock)

select idx as IDX

, division as Division

, s_idx as sIDX

, name as Name

, descr as Description

, charge as Charge

, isvalid as isUse

from tb_st_facilities_info with(nolock)

where 1=1 and

(

@searchType like 'name' and @searchWord is not null and name like '%'+@searchWord+'%'

or

@searchWord is null and 1=1

)

/************************************************************  

0. 결과처리  

************************************************************/  

if ( @@error > 0 )--//실패  

begin  

return 0  

end  

else --//성공  

begin  

return -1  

end

end

select        

a.idx,        

a.personal_key,        

c.member_reg_date,        

a.member_id,        

isnull(d.member_name,'') as member_name,        

isnull(d.member_hp,'') as member_hp,  

c.member_client_ip as memberClientIP  

from tb_cmp_join a with(nolock)        

inner join t_personal b with(nolock)        

on a.personal_key = b.personal_key

inner join t_member c with(nolock)

on a.member_id = c.member_id

left outer join tb_cmp_apply d with(nolock) on a.personal_key = d.personal_key

where 1=1

and

(

@searchWord = '' and 1=1

or

@searchWord <> '' and @searchType like 'id' and c.member_id like '%'+@searchWord+'%'

or

@searchWord <> '' and @searchType like 'hp' and d.member_hp like '%'+@searchWord+'%'

or

@searchWord <> '' and @searchType like 'name' and d.member_name like '%'+@searchWord+'%'

)

페이징 추가

/*********************************************************************  

name : [up_data_request_private_list]  

auth : jang jeong-hun  

date : 2014-01-20

desc : 내 자료 요청 목록

  

exec up_data_request_private_list 'jjh5400823', 0, 1, 15

*********************************************************************/  

alter proc up_data_request_private_list  

@user_id varchar(12),

@category_code int,

@currentPageNumber int,

@pageSize int

as

begin

-- 총 갯수

declare @totalCount int

select @totalCount=count(*)

from tb_data_request_list as a    

left join tb_data_request_category_info as b  

on a.category_code = b.category_code

where a.del_yn = 'N' and a.user_id = @user_id

-- 시작 커서Seq와 끝Seq 설정

declare @startNumber int

declare @endNumber int

set @startNumber = @totalCount - ((@currentPageNumber-1)*@pageSize)

set @endNumber = @totalCount - @pageSize  

select *, @totalCount as totalItem

from ( 

select

a.seq

, a.user_id  

, a.memo  

, a.reg_date  

, b.category_name

, a.state

, row_number() over(order by a.seq asc) as rowNumber

from tb_data_request_list as a    

left join tb_data_request_category_info as b  

on a.category_code = b.category_code

where a.del_yn = 'N' and a.user_id = @user_id and (

@category_code = 0 and 1=1

or

@category_code <> 0 and a.category_code = @category_code

)) as temp

where temp.rowNumber < @startNumber and temp.rowNumber >= @endNumber

order by rowNumber desc

end