--查詢照片
alter proc s_photos_SelectWhere
@currentPage int,
@pageSize int,
@count int output ,
@pDesc varchar(250),
@abId int,
@isPublic int,
@typeId int
as
declare @tempWhereSql_a Nvarchar(300)	--以and開頭的sql條件
declare @tempWhereSql_w Nvarchar(300)	--以where開頭的sql條件
set @tempWhereSql_a = '_'
set @tempWhereSql_w = ''
if (@abId <> -1)
begin
  set @tempWhereSql_a = @tempWhereSql_a + ' and abId=' + convert(varchar(10),@abId)
end
if(@abId = -1 and @typeId <> -1)
begin
  set @tempWhereSql_a = @tempWhereSql_a + ' and abId in (select abId from Album where typeId=' + convert(varchar(10),@typeId)+')'
end
if(@abId = -1 and @typeId = -1)
begin
  set @tempWhereSql_a = @tempWhereSql_a + ' and abId in (select abId from Album)'
end
if (@isPublic <> -1)
begin
  set @tempWhereSql_a = @tempWhereSql_a + ' and isPublic=' + convert(varchar(10),@isPublic)
end
if (@pDesc <> '')
begin
  set @tempWhereSql_a = @tempWhereSql_a + ' and pDesc like ''%' + @pDesc
         + '%''' 
end

if (@tempWhereSql_a = '_')
begin
  set @tempWhereSql_a = '';
end
else
begin
  set @tempWhereSql_w = replace(@tempWhereSql_a, '_ and', ' where ')
  set @tempWhereSql_a = replace(@tempWhereSql_a, '_ and', ' and ')
end
--組合where條件 end

--計算總記錄數 start
--注意此處使用了系統存儲過程sp_executesql來處理動態sql中有輸出參數的情況
declare @tempSql Nvarchar(300)
set @tempSql = 'select @rc=count(*) from Photos ' + @tempWhereSql_w
exec sp_executesql @tempSql, N'@rc int output', @count output
--計算總記錄數 end

--查詢1頁數據 start
declare @temp int
set @temp = (@currentPage-1) * @pagesize
exec('select top ' + @pagesize + ' * from Photos where pId not in
(
 select top ' + @temp + ' pId from photos ' + @tempWhereSql_w +  '
 order by pId desc
) ' + @tempWhereSql_a +  '
order by pId desc')
--查詢1頁數據 end
go

--test
declare @temp int
exec s_photos_SelectWhere 1, 3, @temp output,'',-1,-1,-1
print @temp
go

--查詢相冊

create proc s_Album_SelectWhere
@currentPage int,
@pageSize int,
@count int output ,
@abName varchar(30),
@typeId int,
@userId int
as
declare @tempWhereSql_a Nvarchar(300)	--以and開頭的sql條件
declare @tempWhereSql_w Nvarchar(300)	--以where開頭的sql條件
set @tempWhereSql_a = '_'
set @tempWhereSql_w = ''
if (@typeId <> -1)
begin
  set @tempWhereSql_a = @tempWhereSql_a + ' and typeId=' + convert(varchar(10),@typeId)
end
if (@userId <> -1)
begin
  set @tempWhereSql_a = @tempWhereSql_a + ' and userId=' + convert(varchar(10),@userId)
end
if (@abName <> '')
begin
  set @tempWhereSql_a = @tempWhereSql_a + ' and alName like ''%' + @abName
         + '%''' 
end

if (@tempWhereSql_a = '_')
begin
  set @tempWhereSql_a = '';
end
else
begin
  set @tempWhereSql_w = replace(@tempWhereSql_a, '_ and', ' where ')
  set @tempWhereSql_a = replace(@tempWhereSql_a, '_ and', ' and ')
end
--組合where條件 end

--計算總記錄數 start
--注意此處使用了系統存儲過程sp_executesql來處理動態sql中有輸出參數的情況
declare @tempSql Nvarchar(300)
set @tempSql = 'select @rc=count(*) from Album ' + @tempWhereSql_w
exec sp_executesql @tempSql, N'@rc int output', @count output
--計算總記錄數 end

--查詢1頁數據 start
declare @temp int
set @temp = (@currentPage-1) * @pagesize
exec('select top ' + @pagesize + ' * from Album where abId not in
(
 select top ' + @temp + ' abId from Album ' + @tempWhereSql_w +  '
 order by abId desc
) ' + @tempWhereSql_a +  '
order by abId desc')
--查詢1頁數據 end
go

--test
declare @temp int
exec s_Album_SelectWhere 1, 3, @temp output,'', -1, -1
print @temp
go



select conId from SendBox  where senderId=6
select conId from SendBox  where senderId=@senderId