实体类的各个属性OR关系search PROC IN SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

 

 

 

 

ALTER             proc User_ConditionalLineSearch
(
 @UserID  int,
 @UserName nvarchar(100),
 @PassWord nvarchar(100),
 @RegistDate datetime,
 @Email  nvarchar(500),
 @Tel  nvarchar(500),
 @RealName nvarchar(100),
 @NickName nvarchar(500),
 @Type  int
)
AS

Declare @QueryString NVarchar(2000)
Declare @ParaCount int
Declare @tmpCount int

set @ParaCount = 0
set @tmpCount = 0
if ( @UserID > 0 )
begin
set @ParaCount = @ParaCount+1
end

if ( len(@UserName) > 0 )
begin
set @ParaCount = @ParaCount+1
end

if (len(@PassWord) > 0 )
begin
set @ParaCount = @ParaCount+1
end

if ( datediff (day,@RegistDate,'1900-1-1') != 0)
begin
set @ParaCount = @ParaCount+1
end

if (len(@Email) > 0 )
begin
set @ParaCount = @ParaCount+1
end

if (len(@Tel)>0)
begin
set @ParaCount = @ParaCount+1
end

if (len(@RealName)>0)
begin
set @ParaCount = @ParaCount+1
end

if (len(@NickName)>0)
begin
set @ParaCount = @ParaCount+1
end

set @QueryString ='select * from [User] where 1=0 or '
IF ( @Type = 0 ) --字符串或者数字的等于模式
BEGIN
 
 if ( @UserID > 0 )
 begin 
 set @QueryString = @QueryString + 'UserID='+cast(@UserID as nvarchar)
 
 set @tmpCount = @tmpCount+1
 if ( @tmpCount !=  @ParaCount )
  begin
  set @QueryString = @QueryString +' or '
  end
 end
 
 if ( len(@UserName) > 0 )
 begin
 set @QueryString = @QueryString +  begin
 set @QueryString=@QueryString+'datediff(day, convert ( datetime, '''+ convert ( nvarchar(12),@RegistDate ,1 )  +''',1),RegistDate) = 0'
 set @tmpCount=@tmpCount+1
 if ( @tmpCount !=  @ParaCount )
  begin
  set @QueryString = @QueryString +' or '
  end
 end

 if ( len(@Email) > 0 )
 begin
 set @QueryString = @QueryString +

'+'''%'+@UserName+'%'''
 set @tmpCount = @tmpCount+1
 if ( @tmpCount !=  @ParaCount )
  begin
  set @QueryString = @QueryString +' or '
  end
 end

 if ( len(@PassWord) > 0 )
 begin
 set @QueryString = @QueryString + 'PassWord like '+'''%'+@PassWord+'%'''
 set @tmpCount = @tmpCount+1
 if ( @tmpCount !=  @ParaCount )
  begin
  set @QueryString = @QueryString +'or '
  end
 end
 if ( datediff (day,@RegistDate,'1900-1-1') != 0)
 begin
 set @QueryString=@QueryString+'datediff(day, convert ( datetime, '''+ convert ( nvarchar(12),@RegistDate ,1 )  +''',1),RegistDate) = 0'
 set @tmpCount=@tmpCount+1
 if ( @tmpCount !=  @ParaCount )
  begin
  set @QueryString = @QueryString +'or '
  end
 end
 if ( len(@Email) > 0 )
 begin
 set @QueryString = @QueryString + 'Email like '+'''%'+@Email+'%'''
 set @tmpCount = @tmpCount+1
 if ( @tmpCount !=  @ParaCount )
  begin
  set @QueryString = @QueryString +'or '
  end
 end
 
 if ( len(@Tel) > 0 )
 begin
 set @QueryString = @QueryString + 'Tel like '+'''%'+@Tel+'%'''
 set @tmpCount = @tmpCount+1
 if ( @tmpCount !=  @ParaCount )
  begin
  set @QueryString = @QueryString +'or '
  end
 end
 
 if ( len(@RealName) > 0 )
 begin
 set @QueryString = @QueryString + 'RealName like '+'''%'+@RealName+'%'''
 set @tmpCount = @tmpCount+1
 if ( @tmpCount !=  @ParaCount )
  begin
  set @QueryString = @QueryString +'or '
  end
 end
 
 if ( len(@NickName) > 0 )
 begin
 set @QueryString = @QueryString + 'NickName like '+'''%'+@NickName+'%'''
 set @tmpCount = @tmpCount+1
 if ( @tmpCount !=  @ParaCount )
  begin
  set @QueryString = @QueryString +'or '
  end
 end
END
--select (@QueryString)
exec(@QueryString)

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO