菜鸟求助（关于多条件搜索分页存储过程的优化）
关于多条件搜索存储过程分页实现及优化，求助！
以下是我实际应用中的存储过程，代码非常臃肿，关键是取下一页最后一条数据的ID，条件不用用变量来得到，所以就根据条件分别判断，造成每增加一个条件，代码就2倍增加。希望各位达人能够赐教，小弟不胜感激。
Code
ALTER PROCEDURE [dbo].[Search_UserPage]
@F_Nick nvarchar(100),
@F_Gender tinyint,
@NetType tinyint,
@Page int,
@PageSize int,
@Province nvarchar(50),
@Job nvarchar(50),
@OutCount int OUTPUT
AS
BEGIN
DECLARE @ID bigint
DECLARE @DoWhere nvarchar(500)
DECLARE @SQL nvarchar(1000)
SET @DoWhere =' WHERE 1=1'
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
--搜索条件
IF @F_Nick <>''
BEGIN
SET @DoWhere = @DoWhere + ' AND F_Nick like ''%/'+ @F_Nick +'%''escape ''/'''
END
IF @F_Gender <>2
BEGIN
SET @DoWhere = @DoWhere + ' AND F_Gender='+convert(nvarchar(50),@F_Gender)
END
IF @NetType<>0
BEGIN
SET @DoWhere = @DoWhere + ' AND ((F_NetType & '+convert(nvarchar(50),@NetType)+')!=0)'
END
IF @Province<>''
BEGIN
SET @DoWhere = @DoWhere +' AND F_Province='''+@Province+''''
END
IF @Job<>''
BEGIN
SET @DoWhere = @DoWhere +' AND F_Job='''+@Job+''''
END
-- print (@DoWhere)
--搜索条件结束
IF @Page = 1 --第一页
BEGIN
set @SQL='SELECT TOP('+convert(nvarchar(100),@PageSize)+') * FROM T_User'+ @DoWhere +' ORDER BY F_UID '
exec(@SQL)
END
ELSE --取下一页，就是从这里开始的，
BEGIN
--取最后一条ID
IF @F_Nick <>''
BEGIN
IF @F_Gender <>2
begin
IF @NetType<>0
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and (F_NetType & @NetType)!=0 and F_Province =@Province
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and (F_NetType & @NetType)!=0 and F_Province =@Province
ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and (F_NetType & @NetType)!=0 and F_Job=@Job
ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and (F_NetType & @NetType)!=0
ORDER BY F_UID
end
end
end
else
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
ORDER BY F_UID
end
end
end
end
else
begin
IF @NetType<>0
BEGIN
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/' AND (F_NetType & @NetType)!=0
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/' AND (F_NetType & @NetType)!=0
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/' AND (F_NetType & @NetType)!=0
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/' AND (F_NetType & @NetType)!=0
ORDER BY F_UID
end
end
END
ELSE
BEGIN
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/'
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/'
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/'
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/'
ORDER BY F_UID
end
end
END
end
END
else
begin
IF @F_Gender <>2
begin
IF @NetType<>0
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and (F_NetType & @NetType)!=0
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and (F_NetType & @NetType)!=0
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and (F_NetType & @NetType)!=0
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and (F_NetType & @NetType)!=0
ORDER BY F_UID
end
end
end
else
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender ORDER BY F_UID
end
end
end
end
else
begin
IF @NetType<>0
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE (F_NetType & @NetType)!=0
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE (F_NetType & @NetType)!=0
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE (F_NetType & @NetType)!=0
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE (F_NetType & @NetType)!=0
ORDER BY F_UID
end
end
end
else
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User where F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User where F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User where F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User ORDER BY F_UID
end
end
end
end
end
--这里结束的
SET @SQL='SELECT TOP('+str(@PageSize)+') * FROM T_User '+ @DoWhere +' and F_UID>'+ str(@ID) +' ORDER BY F_UID '
exec(@SQL)
END
SET @OutCount=22
SET XACT_ABORT OFF
SET NOCOUNT OFF
END
CodeALTER PROCEDURE [dbo].[Search_UserPage]
@F_Nick nvarchar(100),
@F_Gender tinyint,
@NetType tinyint,
@Page int,
@PageSize int,
@Province nvarchar(50),
@Job nvarchar(50),
@OutCount int OUTPUT
AS
BEGIN
DECLARE @ID bigint
DECLARE @DoWhere nvarchar(500)
DECLARE @SQL nvarchar(1000)
SET @DoWhere =' WHERE 1=1'
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
--搜索条件
IF @F_Nick <>''
BEGIN
SET @DoWhere = @DoWhere + ' AND F_Nick like ''%/'+ @F_Nick +'%''escape ''/'''
END
IF @F_Gender <>2
BEGIN
SET @DoWhere = @DoWhere + ' AND F_Gender='+convert(nvarchar(50),@F_Gender)
END
IF @NetType<>0
BEGIN
SET @DoWhere = @DoWhere + ' AND ((F_NetType & '+convert(nvarchar(50),@NetType)+')!=0)'
END
IF @Province<>''
BEGIN
SET @DoWhere = @DoWhere +' AND F_Province='''+@Province+''''
END
IF @Job<>''
BEGIN
SET @DoWhere = @DoWhere +' AND F_Job='''+@Job+''''
END
-- print (@DoWhere)
--搜索条件结束
IF @Page = 1 --第一页
BEGIN
set @SQL='SELECT TOP('+convert(nvarchar(100),@PageSize)+') * FROM T_User'+ @DoWhere +' ORDER BY F_UID '
exec(@SQL)
END
ELSE --取下一页，就是从这里开始的，
BEGIN
--取最后一条ID
IF @F_Nick <>''
BEGIN
IF @F_Gender <>2
begin
IF @NetType<>0
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and (F_NetType & @NetType)!=0 and F_Province =@Province
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and (F_NetType & @NetType)!=0 and F_Province =@Province
ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and (F_NetType & @NetType)!=0 and F_Job=@Job
ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and (F_NetType & @NetType)!=0
ORDER BY F_UID
end
end
end
else
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE
F_Nick like '%/'+ @F_Nick +'%'escape '/' and F_Gender=@F_Gender
ORDER BY F_UID
end
end
end
end
else
begin
IF @NetType<>0
BEGIN
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/' AND (F_NetType & @NetType)!=0
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/' AND (F_NetType & @NetType)!=0
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/' AND (F_NetType & @NetType)!=0
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/' AND (F_NetType & @NetType)!=0
ORDER BY F_UID
end
end
END
ELSE
BEGIN
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/'
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/'
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/'
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Nick like '%/'+ @F_Nick +'%'escape '/'
ORDER BY F_UID
end
end
END
end
END
else
begin
IF @F_Gender <>2
begin
IF @NetType<>0
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and (F_NetType & @NetType)!=0
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and (F_NetType & @NetType)!=0
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and (F_NetType & @NetType)!=0
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and (F_NetType & @NetType)!=0
ORDER BY F_UID
end
end
end
else
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User
WHERE F_Gender=@F_Gender ORDER BY F_UID
end
end
end
end
else
begin
IF @NetType<>0
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE (F_NetType & @NetType)!=0
and F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE (F_NetType & @NetType)!=0
and F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE (F_NetType & @NetType)!=0
and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User WHERE (F_NetType & @NetType)!=0
ORDER BY F_UID
end
end
end
else
begin
IF @Province<>''
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User where F_Province =@Province and F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User where F_Province =@Province ORDER BY F_UID
end
end
else
begin
IF @Job<>''
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User where F_Job=@Job ORDER BY F_UID
end
else
begin
SELECT TOP((@Page - 1) * @PageSize) @ID=F_UID FROM T_User ORDER BY F_UID
end
end
end
end
end
--这里结束的
SET @SQL='SELECT TOP('+str(@PageSize)+') * FROM T_User '+ @DoWhere +' and F_UID>'+ str(@ID) +' ORDER BY F_UID '
exec(@SQL)
END
SET @OutCount=22
SET XACT_ABORT OFF
SET NOCOUNT OFF
END
