Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    16

    Unanswered: Query issue = vs list

    Hi All

    I have a stored proc where I need to sometimes use a <>
    This is a MUCH simplified verison of my proc. The real one has many fields and about 5 joins

    I don't want to have 2 queries inside the proc using an IF statement. Is there another way?

    CREATE PROCEDURE myProc (intGetZeroBalance int )

    IF intGetZeroBalance = 1

    BEGIN
    SELECT PATIENT_NAME, BILL_TO_COMPANY
    FROM PATIENT_AR
    WHERE PATIENT_NAME = 'SMITH'
    AND BALANCE <> 0
    END

    ELSE

    BEGIN
    SELECT PATIENT_NAME, BILL_TO_COMPANY
    FROM PATIENT_AR
    WHERE PATIENT_NAME = 'SMITH'
    END

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this idea?

    create table test2(id int,balance money)
    go
    insert test2 select 1,100
    insert test2 select 2,100
    insert test2 select 3,0
    insert test2 select 4,100
    insert test2 select 5,100
    go
    declare @balance money
    set @balance=0
    select * from test2
    where balance <> case when @balance is null then balance+1 else @balance end

  3. #3
    Join Date
    Sep 2003
    Location
    Portland, Oregon
    Posts
    11

    Re: Query issue = vs list

    Originally posted by CaptainEstock
    Hi All

    I have a stored proc where I need to sometimes use a <>
    This is a MUCH simplified verison of my proc. The real one has many fields and about 5 joins

    I don't want to have 2 queries inside the proc using an IF statement. Is there another way?

    CREATE PROCEDURE myProc (intGetZeroBalance int )

    IF intGetZeroBalance = 1

    BEGIN
    SELECT PATIENT_NAME, BILL_TO_COMPANY
    FROM PATIENT_AR
    WHERE PATIENT_NAME = 'SMITH'
    AND BALANCE <> 0
    END

    ELSE

    BEGIN
    SELECT PATIENT_NAME, BILL_TO_COMPANY
    FROM PATIENT_AR
    WHERE PATIENT_NAME = 'SMITH'
    END
    Try this:
    Code:
    CREATE PROCEDURE myProc (intGetZeroBalance int )
    
      SELECT PATIENT_NAME, BILL_TO_COMPANY
      FROM PATIENT_AR
      WHERE PATIENT_NAME = 'SMITH'
      AND ((intGetZeroBalance = 1 AND BALANCE <> 0)
      OR intGetZeroBalance <> 1)
     END
    The naming seems odd. If "GetZeroBalance" = 1, then you exclude records where BALANCE = 0. Isn't that backwards?

    Rob

  4. #4
    Join Date
    Jun 2002
    Posts
    16

    Thanks!

    AND ((intGetZeroBalance = 1 AND BALANCE <> 0)
    OR intGetZeroBalance <> 1)

    THis will work just fine!!

    Thanks!
    CaptainEstock!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •