where clause

Posted on 2009-04-27
Last Modified: 2012-05-06

How can I say if @projectID has no value then select * otherwise select what ever the variable passing?

here's what I have.

insert into #TempData (acct,ProjectID,Sub,Perpost,CrAmt,DrAmt)
SELECT  dbo.GLTran.acct,dbo.GLTran.ProjectID,dbo.GLTran.Sub,dbo.GLTran.PerPost,
      CASE WHEN (SUM(CrAmt) - SUM(DrAmt)) >= 0 THEN (SUM(CrAmt) - SUM(DrAmt)) ELSE 0 END AS CrAmt,
    CASE WHEN (SUM(CrAmt) - SUM(DrAmt)) < 0 THEN (SUM(DrAmt) - SUM(CrAmt)) ELSE 0 END AS DrAmt
FROM         dbo.Account INNER JOIN
                      dbo.GLTran ON dbo.Account.Acct = dbo.GLTran.Acct
WHERE     (dbo.Account.Acct like '4%' and (dbo.Account.Acct not in (4410,4600))) AND (dbo.GLTran.LedgerID = 'Actual')
            AND (dbo.GLTran.PerPost BETWEEN @BgDate AND @EndDate) AND
         (dbo.GLTran.JrnlType <> 'AR')
      and  dbo.GLTran.ProjectID = Rtrim(@ProjectID)

so in the where clause.  it says
and  dbo.GLTran.ProjectID = Rtrim(@ProjectID)
that line is not working I want to say if the projectID has no passed value then select *
otherwise select what's been passing.


Question by:lulu50
    LVL 41

    Expert Comment

    You can try something like this:
    and  (@ProjectID <> 0 and (dbo.GLTran.ProjectID = Rtrim(@ProjectID))
    and  (@ProjectID is not null and (dbo.GLTran.ProjectID = Rtrim(@ProjectID))
    depending on the data type of @projectID
    LVL 44

    Accepted Solution

    Hello lulu50,

    And (dbo.GLTran.ProjectID = RTrim(@ProjectID) Or @ProjectID Is Null)

    Should do it


    LVL 41

    Expert Comment

    Sorry missed brackets there:

    and  (@ProjectID <> 0 and (dbo.GLTran.ProjectID = Rtrim(@ProjectID))) -- if @projectID is int


    and  (@ProjectID is not null and (dbo.GLTran.ProjectID = Rtrim(@ProjectID)))  -- if @projectID is varchar

    Author Closing Comment

    Thank you
    LVL 41

    Expert Comment

    I'm sorry but TIM cotte is the same solution as mine. you should split points in this case
    LVL 41

    Expert Comment

    Would you mind reopen the question for further discussion?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now