sms存储过程.sql

企业内部的短信交流管理平台。 设计详细

--查询照片
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