求一段用存储过程实现高级搜索代码?
现在有一个表名为:user
字段有:user_id, user_name, user_birth,user_email

要实现根据从来的值在存储过程中进行查询并分页
IF (USER_NAME!= " ")
SELECT *FROM USER WHERE USER_NAME=@USER_NAME
;;;
IF(USER_EMAIL!= " ")
SELECT *FROM USER WHERE USER_EMAIL=@USER_EMAIL
.........
.......
这样的话不知要写多少行.我记得能用+=.我不知道应该怎么样写了.
请大家帮下我吧.



[解决办法]
这个实际上就是模糊查询，如果做个网站是多用户的话，模糊查询用的多些。请参考下面我的代码，修改下字段和参数就可以使用了。呵呵！！
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
########################用户表组合查询###################################

CREATE proc sp_searchUser
(
@UserName varchar(25)=null,
@UserSchool varchar(20)=null,
@UserEmail varchar(50)=null,
@UserQQ varchar(20)=null,
@UserMSN varchar(50)=null,
@UserCard varchar(20)=null,
@UserRank varchar(10)=null
)
as
declare @sqlstr varchar(1000)
set @sqlstr= 'select * from uupo_User where 1=1 '
if (@UserName <> ' ')
set @sqlstr=@sqlstr + 'and User_name like "% '+@UserName+ '% " '
if (@UserSchool <> ' ')
set @sqlstr=@sqlstr + 'and User_School like "% '+@UserSchool+ '% " '
if (@UserEmail <> ' ')
set @sqlstr=@sqlstr + 'and User_email like "% '+@UserEmail+ '% " '
if (@UserQQ <> ' ')
set @sqlstr=@sqlstr + 'and User_qq like "% '+@UserQQ+ '% " '
if (@UserMSN <> ' ')
set @sqlstr=@sqlstr + 'and User_Msn like "% '+@UserMSN+ '% " '
if (@UserCard <> ' ')
set @sqlstr=@sqlstr + 'and User_card like "% '+@UserCard+ '% " '
if (@UserRank <> ' ')
set @sqlstr=@sqlstr + 'and User_rank like "% '+@UserRank+ '% " '

exec(@sqlstr)
GO

[解决办法]
CREATE proc StaffSearch
@Name varchar(200),
@Mobile varchar(100),
@Email varchar(100),
@Gender varchar(100),
@PositionID int,
@SearchBound varchar(50)
/*--------------------------------------
功能：查询人员
参数：
@Name人员姓名
@Mobile手机号码
@Emailemail
@Gender性别
@PositionID职位id
@SearchBound查询范围
---------------------------------------*/
AS

DECLARE @SQL VARCHAR(500)
DECLARE @HAVECONDITION BIT

IF @PositionID=0
SET @SQL = 'SELECT UDS_Position.Position_Name,uds_staff.*,Case Sex When 1 Then ' '男 ' ' Else ' '女 ' ' End AS SexName FROM UDS_STAFF Left OUTER JOIN UDS_Staff_In_Position ON UDS_STAFF.STAFF_ID=UDS_Staff_In_Position.Staff_ID INNER JOIN UDS_Position ON UDS_Staff_In_Position.Position_ID=UDS_Position.Position_ID '
ELSE
SET @SQL = 'SELECT UDS_Position.Position_Name,uds_staff.*,Case Sex When 1 Then ' '男 ' ' Else ' '女 ' ' End AS SexName FROM UDS_STAFF INNER JOIN UDS_Staff_In_Position ON UDS_STAFF.STAFF_ID=UDS_Staff_In_Position.Staff_ID INNER JOIN UDS_Position ON UDS_Staff_In_Position.Position_ID=UDS_Position.Position_ID '

SET @HAVECONDITION = 0
IF (@Name <> ' ')
BEGIN
SET @SQL = @SQL + ' WHERE (Staff_Name LIKE ' '% '+@Name+ '% ' ' OR RealName LIKE ' '% '+@Name+ '% ' ') '
SET @HAVECONDITION = 1
END

IF (@Mobile <> ' ')
BEGIN
IF(@HAVECONDITION=0)
BEGIN
SET @SQL = @SQL + ' WHERE (Mobile LIKE ' '% '+@Mobile+ '% ' ') '
SET @HAVECONDITION = 1
END
ELSE
SET @SQL = @SQL + ' AND (Mobile LIKE ' '% '+@Mobile+ '% ' ') '
END

IF (@Email <> ' ')
BEGIN
IF(@HAVECONDITION=0)
BEGIN
SET @SQL = @SQL + ' WHERE (Email LIKE ' '% '+@Email+ '% ' ') '
SET @HAVECONDITION = 1
END
ELSE
SET @SQL = @SQL + ' AND (Email LIKE ' '% '+@Email+ '% ' ') '
END

IF (@Gender <> ' ')
BEGIN
IF(@HAVECONDITION=0)
BEGIN
IF @Gender= 'male '
BEGIN
SET @SQL = @SQL + ' WHERE (Sex=1) '
SET @HAVECONDITION = 1
END
ELSE
BEGIN
SET @SQL = @SQL + ' WHERE (Sex=0) '
SET @HAVECONDITION = 1
END

END
ELSE
IF @Gender= 'male '
BEGIN
SET @SQL = @SQL + ' AND (Sex=1) '

END
ELSE
BEGIN
SET @SQL = @SQL + ' AND (Sex=0) '

END

END

IF (@PositionID <> 0)
BEGIN
IF(@HAVECONDITION=0)
BEGIN
SET @SQL = @SQL + ' WHERE (UDS_Position.Position_ID= '+Convert(varchar(5),@PositionID)+ ') '
SET @HAVECONDITION = 1
END
ELSE
SET @SQL = @SQL + ' AND (UDS_Position.Position_ID= '+Convert(varchar(5),@PositionID)+ ') '
END

IF (@SearchBound= 'on ')
BEGIN
IF(@HAVECONDITION=0)
BEGIN
SET @SQL = @SQL + ' WHERE (UDS_Staff.Dimission=0) '
SET @HAVECONDITION = 1
END
ELSE
SET @SQL = @SQL + ' AND (UDS_Staff.Dimission=0) '
END

IF (@SearchBound= 'off ')
BEGIN
IF(@HAVECONDITION=0)
BEGIN
SET @SQL = @SQL + ' WHERE (UDS_Staff.Dimission=1) '
SET @HAVECONDITION = 1
END
ELSE
SET @SQL = @SQL + ' AND (UDS_Staff.Dimission=1) '
END

PRINT @SQL
EXECUTE (@SQL)

GO
[解决办法]
要两次select