动态的添加条件实现查询

--动态的添加条件

--UP3_ChildUser_ByTDetIdUserName '888888','','',156

ALTER PROC [dbo].[UP3_ChildUser_ByTDetIdUserName]

@comID varchar(50),

@TDetId INT,

@Name VARCHAR(50),

@userId INT

AS

DECLARE @where VARCHAR(5000)

DECLARE @dep INT

SELECT @comID=comNO FROM dbo.ChildUser WHERE id=@userId;

 

SELECT @dep=DepartmentID FROM dbo.ChildUser WHERE id=@userId;

 

with my1 as(select TDtID from dbo.TDept where TDtID = @dep

union all select TDept.TDtID from my1, TDept where my1.TDtID = TDept.TDtPID )

SELECT * INTO #TDetId2  FROM my1

 

SET @where=' SELECT t.TDtID,t.TDtName,c.id,substring(Convert(varchar,c.addtime,120),1,10) as addtime ,c.name,c.username,c.password ,c.telphone ,c.sex, c.cardID ,c.email,c.birthday,c.native,isWorkState

       FROM dbo.ChildUser  c,dbo.TDept t

       WHERE c.DepartmentID=t.TDtID '

 

IF(@TDetId!='')

    BEGIN

       IF(@TDetId!=0)

           BEGIN

              SET @where=@where+' and t.TDtID ='+LTRIM(RTRIM(@TDetId))+' order by c.id desc '

           END

       else IF(@Name!='')

           BEGIN

              SET @where=@where+' and c.name like'+CHAR(39)+'%'+@Name+'%'+CHAR(39)+' order by c.id desc '--+ 'and  c.comNO='+LTRIM(RTRIM(@comID))

           END

       EXEC(@where)

       DROP TABLE #TDetId2

    END

ELSE

    BEGIN

       IF(@Name!='')

           BEGIN

              SET @where=@where+' and c.name like'+CHAR(39)+'%'+@Name+'%'+CHAR(39)+'and c.comNO='+LTRIM(RTRIM(@comID))+'order  by c.id desc' 

           END

       else

           begin

              SET @where=@where+'and c.comNO='+LTRIM(RTRIM(@comID))+' order  by c.id desc' 

           end

       EXEC(@where)

       DROP TABLE #TDetId2

    END