如何编写查询以满足搜索条件 [英] how to write a query to satisfy search conditon

问题描述

我需要建议来编写满足这些条件的查询.

I need suggestion to write a query which satisfy these condition's.

我将三个参数传递给存储过程:

I am passing three parameters to a stored procedure:

• 至今
• 从日期
• 已激活

要求是这样的:

• 在页面加载时显示谁是今天的注册商
• 仅通过 to date 然后我只需要显示那些注册到 date 的人
• 在传递from date时只显示那个日期之后的那些
• 对于两者来说，只有那些介于两者之间的人

• on page load show who is regisatar today
• on passing only to date then i need to show only those who registar till date
• on passing from date show only those who are after that date
• and for both then only those who fall between them

我试过了，但那完全是浪费.

I tried but that is complete waste.

If (@viewPending = 1 or @viewPending = 0 )
    Begin
        IF @TDate IS NULL AND @FDate IS NOT NULL
            SELECT 
            RegDate,
            CenterName  As [Center Name],
            OwnerName   As [Owner Name],
            MobileNo    As [Mobile],
            MailID      As [EMail ID],
            isVerified  As [Verified]
        FROM  TBL_iREGFORM
        WHERE REGDATE >= @FDate AND isVerified in (Case When  @viewPending =1 Then 0 Else 1 | 0 End) 
      IF @TDate IS NOT NULL AND @FDate IS  NULL
            SELECT 
            RegDate,
            CenterName  As [Center Name],
            OwnerName   As [Owner Name],
            MobileNo    As [Mobile],
            MailID      As [EMail ID],
            isVerified  As [Verified]
        FROM  TBL_iREGFORM
        WHERE REGDATE <= @FDate AND isVerified in (Case When  @viewPending =1 Then 0 Else 1 | 0 End)
       IF @TDate IS NOT NULL AND @FDate IS NOT NULL
            SELECT 
            RegDate,
            CenterName  As [Center Name],
            OwnerName   As [Owner Name],
            MobileNo    As [Mobile],
            MailID      As [EMail ID],
            isVerified  As [Verified]
        FROM  TBL_iREGFORM
        WHERE REGDATE BETWEEN  @FDate AND @TDATE AND isVerified in (Case When  @viewPending =1 Then 0 Else 1 | 0 End)
    ELSE

            SELECT 
            RegDate,
            CenterName  As [Center Name],
            OwnerName   As [Owner Name],
            MobileNo    As [Mobile],
            MailID      As [EMail ID],
            isVerified  As [Verified]
        FROM  TBL_iREGFORM
        WHERE REGDATE =CONVERT(VARCHAR(10),GETDATE(),103) AND isVerified in (Case When  @viewPending =1 Then 0 Else 1 | 0 End)

推荐答案

SELECT * FROM
  MyTable
WHERE
  ( @startDate IS NULL OR RegisterDate >= @startDate )
AND ( @endDate IS NULL OR RegisterDate <= @endDate )
AND ( @active IS NULL OR Active = @active )

这篇关于如何编写查询以满足搜索条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！