CODEWITHSUNDEEP
sqlsql-serverstored-procedures
joshua
joshua

Reputation: 2371

Stored procedure unexpected result not desired

ALTER PROCEDURE  [dbo].[USP_ViewRegForm]
    -- Add the parameters for the stored procedure here
    (   @RegFormID      Numeric=null,
        @FDate          Date=null,
        @TDate          Date=null,
        @viewPending        int=null
     )
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET DATEFORMAT DMY;
    -- View statements for procedure here
    -- 
    If (@viewPending = 1 or @viewPending = 0 Or @viewPending is null)
    Begin

            SELECT 
            RegDate,
            iRegFormID    As [Registraion ID],
            CenterName  As [Center Name],
            OwnerName   As [Owner Name],
            MobileNo    As [Mobile],
            MailID      As [EMail ID],
            isVerified  As [Verified]
        FROM  TBL_iREGFORM
        WHERE (@FDate Is Null or RegDate <= @TDate ) And 
              (@TDate is Null or RegDate >= @FDate ) And
              ((RegDate between @FDate and @TDate) OR (RegDate=convert(varchar(20),GETDATE(),103)) )  And
              isVerified in (Case When  @viewPending =1 Then 0 Else 1 | 0 End) 
    End

Problem is here : isVerified in (Case When @viewPending =1 Then 0 Else 1 | 0 End)

If I am not passing in the @viewPenind value then it has to select both 1 and 0. But this returns only one value ..

Why??

Previous question

Upvotes: 1

Views: 89

Answers (1)

Matej
Matej

Reputation: 7627

Change WHERE part to isVerified = COALESCE(@viewPending, isVerified).

1=NULL evaluates as false.

Upvotes: 2

Related Questions