--try
Create Proc P_Test(@flag int,@GroupID int,@UserName varchar(10),@Email varchar(10))
As
Begin
if @flag=0 and @GroupID=0
begin
select * from UserSheet where UserName=@UserName and Email=@Email
end if @flag=0 and @GroupID<>0
begin
select * from UserSheet where GroupID=@GroupID and UserName=@UserName and Email=@Email
end if @flag<>0 and @GroupID=0
begin
select * from UserSheet where UserName like '%'+@UserName+'%' and Email like '%'+@Email+'%'
end if @flag<>0 and @GroupID<>0
begin
select * from UserSheet where GroupID=@GroupID and UserName like '%'+@UserName+'%' and Email like '%'+@Email+'%'
endEnd
Create Proc P_Test(@flag int,@GroupID int,@UserName varchar(10),@Email varchar(10))
As
Begin
if @flag=0 and @GroupID=0
begin
select * from UserSheet where UserName=@UserName and Email=@Email
end if @flag=0 and @GroupID<>0
begin
select * from UserSheet where GroupID=@GroupID and UserName=@UserName and Email=@Email
end if @flag<>0 and @GroupID=0
begin
select * from UserSheet where UserName like '%'+@UserName+'%' and Email like '%'+@Email+'%'
end if @flag<>0 and @GroupID<>0
begin
select * from UserSheet where GroupID=@GroupID and UserName like '%'+@UserName+'%' and Email like '%'+@Email+'%'
endEnd
(@flag bit,
@GroupID int,
@UserName varchar(100),
@Email varchar(100))
as
Declare @v_sql varchar(4000)
set @v_sql='select * from UserSheet 'if @flag=0
set @v_sql=@v_sql+'where UserName='''+@UserName+''''
else
set @v_sql=@v_sql+'where UserName like ''%'+@UserName+'%'''if @GroupID<>0
set @v_sql=@v_sql+' and GroupID='+@GroupID+''if @Email is not null
set @v_sql=@v_sql+' and Email like ''%'+@Email+'%'''Exec(@v_sql)
Go
--测试表--------------------------------------create table UserSheet(ID int,UserName varchar(100),GroupID int,Email varchar(100))--插入数据-------------------------------------
insert UserSheet select 1,'abc',1,'[email protected]'
insert UserSheet select 2,'ab',1,'[email protected]'
insert UserSheet select 3,'a',2,'[email protected]'
insert UserSheet select 4,'b',2,'[email protected]'--修改存储过程----------------------------------
alter proc dbo.P_Find_User
(@flag bit,
@GroupID int,
@UserName varchar(100),
@Email varchar(100))
as
Declare @v_sql varchar(4000)
set @v_sql='select * from UserSheet 'if @flag=0
set @v_sql=@v_sql+'where UserName='''+@UserName+''''
else
set @v_sql=@v_sql+'where UserName like ''%'+@UserName+'%'''if @GroupID<>0
set @v_sql=@v_sql+' and GroupID='+cast(@GroupID as varchar)+''if @Email is not null
set @v_sql=@v_sql+' and Email like ''%'+@Email+'%'''Exec(@v_sql)
Go--执行存储过程----------------------------------Exec dbo.P_Find_User 0,1,'abc','a'