프로시져 동적 쿼리 - 검색어 쿼리
저장 프로시저
/*********************************************************************
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