-- ============================================= -- 名字:Up_Bap_SelectUserAssociationInfo -- 查询协会人员相关信息 -- 输出字段: -- b.UserID, 用户ID -- b.UserName, 用户名 -- a.ActualName, 用户真实姓名 -- a.RegisterDate, 注册时间 -- d.ProvinceID, 省ID -- d.ProvinceName, 省名称 -- e.CityID, 市ID -- e.CityName, 市名称 -- f.CountyID, 县ID -- f.CountyName 县名称 --exec Up_Bap_SelectUserAssociationInfo null,null,null,null,null,null,10,1,null ALTER proc [db_owner].[Up_Bap_SelectUserAssociationInfo] @UserName varchar(32), --注册名 @RegisterFrom varchar(10), --注册时间起 @RegisterTo varchar(10), --注册时间止 @ProvinceID int, --省ID @CityID int, --市ID @CountryID int, --县ID @PageSize int, @RecordIndex int, @AssociationID int --协会ID as declare @Sql varchar(8000) declare @AllSql varchar(8000) set @Sql='' set @AllSql=''--如果注册名不为空 if @UserName is not null set @Sql = @Sql+' and b.UserName like '''+'%'+@UserName+'%'+'''' --判断注册时间起是否为空,不为空则作为查询条件 if @RegisterFrom IS NOT NULL set @sql = @sql + ' AND CONVERT(DATETIME,CONVERT(VARCHAR,a.RegisterDate,112)) >= CONVERT(DATETIME,'''+@RegisterFrom + ''') '
--判断注册时间止是否为空,不为空则作为查询条件 if @RegisterTo IS NOT NULL set @sql = @sql + ' AND CONVERT(DATETIME,CONVERT(VARCHAR,a.RegisterDate,112)) <= CONVERT(DATETIME,'''+@RegisterTo + ''') '--省ID不为空 if @ProvinceID is not null set @Sql = @Sql+' and a.ProvinceID='+convert(varchar,@ProvinceID) --市ID不为空 if @CityID is not null set @Sql = @Sql+' and a.CityID='+convert(varchar,@CityID) --县ID不为空 if @CountryID is not null set @Sql = @Sql+' and a.CountryID='+convert(varchar,@CountryID) --协会ID不为空 if @AssociationID is not null set @Sql = @Sql+' and c.AssociationID='+convert(varchar,@AssociationID) set @AllSql=' select UserID, UserName, ActualName, RegisterDate, ProvinceID, ProvinceName, CityID, CityName, CountyID, AssociationID, RoleID, CountyName, row from ( select DISTINCT b.UserID, b.UserName, a.ActualName, a.RegisterDate, d.ProvinceID, d.ProvinceName, c.AssociationID, c.RoleID, e.CityID, e.CityName, f.CountyID, f.CountyName, ROW_NUMBER() OVER(order by a.RegisterDate desc ) as row from db_owner.UserInfo a inner join db_owner.[User] b on a.UserID = b.UserID inner join db_owner.UserRoleRelation c on a.UserID =c.UserID left join db_owner.ProvinceCode d on a.ProvinceID =d.ProvinceID left join db_owner.City e on a.CityID = e.CityID left join db_owner.County f on a.CountryID =f.CountyID where 1=1 '+@Sql+' ) as #temp 'set @AllSql =@AllSql + ' where row BETWEEN ' + CONVERT(varchar,@RecordIndex) + ' AND ' + CONVERT(varchar,(@RecordIndex+@PageSize-1)) + ' ORDER BY RegisterDate DESC'print(@AllSql) exec(@AllSql)
-- =============================================
-- 名字:Up_Bap_SelectUserAssociationInfo
-- 查询协会人员相关信息
-- 输出字段:
-- b.UserID, 用户ID
-- b.UserName, 用户名
-- a.ActualName, 用户真实姓名
-- a.RegisterDate, 注册时间
-- d.ProvinceID, 省ID
-- d.ProvinceName, 省名称
-- e.CityID, 市ID
-- e.CityName, 市名称
-- f.CountyID, 县ID
-- f.CountyName 县名称
--exec Up_Bap_SelectUserAssociationInfo null,null,null,null,null,null,10,1,null
ALTER proc [db_owner].[Up_Bap_SelectUserAssociationInfo]
@UserName varchar(32), --注册名
@RegisterFrom varchar(10), --注册时间起
@RegisterTo varchar(10), --注册时间止
@ProvinceID int, --省ID
@CityID int, --市ID
@CountryID int, --县ID
@PageSize int,
@RecordIndex int,
@AssociationID int --协会ID
as
declare @Sql varchar(8000)
declare @AllSql varchar(8000)
set @Sql=''
set @AllSql=''--如果注册名不为空
if @UserName is not null
set @Sql = @Sql+' and b.UserName like '''+'%'+@UserName+'%'+'''' --判断注册时间起是否为空,不为空则作为查询条件
if @RegisterFrom IS NOT NULL
set @sql = @sql + ' AND CONVERT(DATETIME,CONVERT(VARCHAR,a.RegisterDate,112)) >= CONVERT(DATETIME,'''+@RegisterFrom + ''') '
--判断注册时间止是否为空,不为空则作为查询条件
if @RegisterTo IS NOT NULL
set @sql = @sql + ' AND CONVERT(DATETIME,CONVERT(VARCHAR,a.RegisterDate,112)) <= CONVERT(DATETIME,'''+@RegisterTo + ''') '--省ID不为空
if @ProvinceID is not null
set @Sql = @Sql+' and a.ProvinceID='+convert(varchar,@ProvinceID)
--市ID不为空
if @CityID is not null
set @Sql = @Sql+' and a.CityID='+convert(varchar,@CityID)
--县ID不为空
if @CountryID is not null
set @Sql = @Sql+' and a.CountryID='+convert(varchar,@CountryID)
--协会ID不为空
if @AssociationID is not null
set @Sql = @Sql+' and c.AssociationID='+convert(varchar,@AssociationID)
set @AllSql='
select
UserID,
UserName,
ActualName,
RegisterDate,
ProvinceID,
ProvinceName,
CityID,
CityName,
CountyID,
AssociationID,
RoleID,
CountyName,
row
from (
select DISTINCT
b.UserID,
b.UserName,
a.ActualName,
a.RegisterDate,
d.ProvinceID,
d.ProvinceName,
c.AssociationID,
c.RoleID,
e.CityID,
e.CityName,
f.CountyID,
f.CountyName,
ROW_NUMBER() OVER(order by a.RegisterDate desc ) as row
from
db_owner.UserInfo a
inner join db_owner.[User] b on a.UserID = b.UserID
inner join db_owner.UserRoleRelation c on a.UserID =c.UserID
left join db_owner.ProvinceCode d on a.ProvinceID =d.ProvinceID
left join db_owner.City e on a.CityID = e.CityID
left join db_owner.County f on a.CountryID =f.CountyID where 1=1 '+@Sql+'
) as #temp 'set @AllSql =@AllSql + ' where row BETWEEN ' + CONVERT(varchar,@RecordIndex) + ' AND ' + CONVERT(varchar,(@RecordIndex+@PageSize-1)) + ' ORDER BY RegisterDate DESC'print(@AllSql)
exec(@AllSql)
gridview里有那个功能好像!!~