请通过逻辑组合实现:条件: ((qy is null//不限制qy) or (qy is not null and qy 限制条件))当qy 等于 null是不做任何限制,
当qy不等于null, 也就是有值的时候, 同时限制条件CREATE PROC PROC_EatSearch
@livename varchar(50),
@qy varchar(50),
@fhts int,
AS
BEGIN
select * from table
where livename = @livename and ((qy is null)or(qy is not null and qy = @qy))and fhts = @fhts
END以下是一个之前修改过的Procdure, 供LZ参考:LTER PROCEDURE [dbo].[GetDataByCriteria]
(
@ProjectId int = NULL,
@ComponentId int = NULL,
@VersionId int = NULL,
@PriorityId int = NULL,
@TypeId int = NULL,
@ResolutionId int = NULL,
@StatusId int = NULL,
@AssignedToUserName nvarchar(256) = NULL,
@Keywords nvarchar(256) = NULL,
@IncludeComments bit = NULL,
@ReporterUserName nvarchar(255) = NULL,
@FixedInVersionId int = NULL,
@DueStartDate DateTime = NULL,
@DueEndDate DateTime = NULL
)
AS DECLARE @AssignedToUserId UNIQUEIDENTIFIER
SELECT @AssignedToUserId = UserId FROM aspnet_users WHERE Username = @AssignedToUserName
DECLARE @ReporterUserId UNIQUEIDENTIFIER
SELECT @ReporterUserId = UserId FROM aspnet_users WHERE Username = @ReporterUserName
/*if @DueStartDate is null
SELECT @DueStartDate = dateadd(month,-2,getdate())
if @DueEndDate is null
SELECT @DueEndDate = dateadd(month,+2,getdate())*/
IF @StatusId = 0SELECT
*
FROM
TableName
WHERE
(
((@ProjectId IS NULL) OR (ProjectId = @ProjectId)) AND
((@ComponentId IS NULL) OR (ComponentId = @ComponentId)) AND
((@VersionId IS NULL) OR (VersionId = @VersionId)) AND
((@FixedInVersionId IS NULL) OR (FixedInVersionId = @FixedInVersionId)) AND
((@PriorityId IS NULL) OR (PriorityId = @PriorityId))AND
((@TypeId IS NULL) OR (TypeId = @TypeId)) AND
((@ResolutionId IS NULL) OR (ResolutionId = @ResolutionId)) AND
((@StatusId IS NULL) OR (StatusId In (1,2,3))) AND
((@ReporterUserId IS NULL) OR (ReporterUserId = @ReporterUserId)) AND
((@Keywords IS NULL) OR (Description LIKE '%' + @Keywords + '%' ) OR (Summary LIKE '%' + @Keywords + '%' ) ) AND
((@AssignedToUserName IS NULL) OR (@AssignedToUserName = '-1' AND AssignedToUserId IS NULL)
OR (AssignedToUserId IS NOT NULL AND AssignedToUserId = @AssignedToUserId))) AND
((@DueStartDate is not null and @DueEndDate is not null and DUEDATE BETWEEN @DueStartDate AND @DueEndDate )
OR (@DueStartDate is not null and @DueEndDate is null and DUEDATE >= @DueStartDate)
OR (@DueStartDate is null and @DueEndDate is not null and DUEDATE <= @DueEndDate)
OR (@DueStartDate is null and @DueEndDate is null)
) ORDER BY PriorityId ASC
ELSESELECT
*
FROM
TableName
WHERE
(
((@ProjectId IS NULL) OR (ProjectId = @ProjectId)) AND
((@ComponentId IS NULL) OR (ComponentId = @ComponentId)) AND
((@VersionId IS NULL) OR (VersionId = @VersionId)) AND
((@FixedInVersionId IS NULL) OR (FixedInVersionId = @FixedInVersionId)) AND
((@PriorityId IS NULL) OR (PriorityId = @PriorityId))AND
((@TypeId IS NULL) OR (TypeId = @TypeId)) AND
((@ResolutionId IS NULL) OR (ResolutionId = @ResolutionId)) AND
((@StatusId IS NULL) OR (StatusId = @StatusId)) AND
((@AssignedToUserName IS NULL) OR (@AssignedToUserName = '-1' AND AssignedToUserId IS NULL)
OR (AssignedToUserId IS NOT NULL AND AssignedToUserId = @AssignedToUserId)) AND
((@ReporterUserId IS NULL) OR (ReporterUserId = @ReporterUserId)) AND
((@Keywords IS NULL) OR (Description LIKE '%' + @Keywords + '%' ) OR (Summary LIKE '%' + @Keywords + '%' )))
AND
((@DueStartDate is not null and @DueEndDate is not null and DUEDATE BETWEEN @DueStartDate AND @DueEndDate )
OR (@DueStartDate is not null and @DueEndDate is null and DUEDATE >= @DueStartDate)
OR (@DueStartDate is null and @DueEndDate is not null and DUEDATE <= @DueEndDate)
OR (@DueStartDate is null and @DueEndDate is null)
)
ORDER BY PriorityId ASC
当qy不等于null, 也就是有值的时候, 同时限制条件CREATE PROC PROC_EatSearch
@livename varchar(50),
@qy varchar(50),
@fhts int,
AS
BEGIN
select * from table
where livename = @livename and ((qy is null)or(qy is not null and qy = @qy))and fhts = @fhts
END以下是一个之前修改过的Procdure, 供LZ参考:LTER PROCEDURE [dbo].[GetDataByCriteria]
(
@ProjectId int = NULL,
@ComponentId int = NULL,
@VersionId int = NULL,
@PriorityId int = NULL,
@TypeId int = NULL,
@ResolutionId int = NULL,
@StatusId int = NULL,
@AssignedToUserName nvarchar(256) = NULL,
@Keywords nvarchar(256) = NULL,
@IncludeComments bit = NULL,
@ReporterUserName nvarchar(255) = NULL,
@FixedInVersionId int = NULL,
@DueStartDate DateTime = NULL,
@DueEndDate DateTime = NULL
)
AS DECLARE @AssignedToUserId UNIQUEIDENTIFIER
SELECT @AssignedToUserId = UserId FROM aspnet_users WHERE Username = @AssignedToUserName
DECLARE @ReporterUserId UNIQUEIDENTIFIER
SELECT @ReporterUserId = UserId FROM aspnet_users WHERE Username = @ReporterUserName
/*if @DueStartDate is null
SELECT @DueStartDate = dateadd(month,-2,getdate())
if @DueEndDate is null
SELECT @DueEndDate = dateadd(month,+2,getdate())*/
IF @StatusId = 0SELECT
*
FROM
TableName
WHERE
(
((@ProjectId IS NULL) OR (ProjectId = @ProjectId)) AND
((@ComponentId IS NULL) OR (ComponentId = @ComponentId)) AND
((@VersionId IS NULL) OR (VersionId = @VersionId)) AND
((@FixedInVersionId IS NULL) OR (FixedInVersionId = @FixedInVersionId)) AND
((@PriorityId IS NULL) OR (PriorityId = @PriorityId))AND
((@TypeId IS NULL) OR (TypeId = @TypeId)) AND
((@ResolutionId IS NULL) OR (ResolutionId = @ResolutionId)) AND
((@StatusId IS NULL) OR (StatusId In (1,2,3))) AND
((@ReporterUserId IS NULL) OR (ReporterUserId = @ReporterUserId)) AND
((@Keywords IS NULL) OR (Description LIKE '%' + @Keywords + '%' ) OR (Summary LIKE '%' + @Keywords + '%' ) ) AND
((@AssignedToUserName IS NULL) OR (@AssignedToUserName = '-1' AND AssignedToUserId IS NULL)
OR (AssignedToUserId IS NOT NULL AND AssignedToUserId = @AssignedToUserId))) AND
((@DueStartDate is not null and @DueEndDate is not null and DUEDATE BETWEEN @DueStartDate AND @DueEndDate )
OR (@DueStartDate is not null and @DueEndDate is null and DUEDATE >= @DueStartDate)
OR (@DueStartDate is null and @DueEndDate is not null and DUEDATE <= @DueEndDate)
OR (@DueStartDate is null and @DueEndDate is null)
) ORDER BY PriorityId ASC
ELSESELECT
*
FROM
TableName
WHERE
(
((@ProjectId IS NULL) OR (ProjectId = @ProjectId)) AND
((@ComponentId IS NULL) OR (ComponentId = @ComponentId)) AND
((@VersionId IS NULL) OR (VersionId = @VersionId)) AND
((@FixedInVersionId IS NULL) OR (FixedInVersionId = @FixedInVersionId)) AND
((@PriorityId IS NULL) OR (PriorityId = @PriorityId))AND
((@TypeId IS NULL) OR (TypeId = @TypeId)) AND
((@ResolutionId IS NULL) OR (ResolutionId = @ResolutionId)) AND
((@StatusId IS NULL) OR (StatusId = @StatusId)) AND
((@AssignedToUserName IS NULL) OR (@AssignedToUserName = '-1' AND AssignedToUserId IS NULL)
OR (AssignedToUserId IS NOT NULL AND AssignedToUserId = @AssignedToUserId)) AND
((@ReporterUserId IS NULL) OR (ReporterUserId = @ReporterUserId)) AND
((@Keywords IS NULL) OR (Description LIKE '%' + @Keywords + '%' ) OR (Summary LIKE '%' + @Keywords + '%' )))
AND
((@DueStartDate is not null and @DueEndDate is not null and DUEDATE BETWEEN @DueStartDate AND @DueEndDate )
OR (@DueStartDate is not null and @DueEndDate is null and DUEDATE >= @DueStartDate)
OR (@DueStartDate is null and @DueEndDate is not null and DUEDATE <= @DueEndDate)
OR (@DueStartDate is null and @DueEndDate is null)
)
ORDER BY PriorityId ASC
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货