where clause

Hi,

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.

Thanks,


lulu50Asked:
Who is Participating?
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
Hello lulu50,

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

Should do it

Regards,

TimCottee
0
 
ralmadaCommented:
You can try something like this:
and  (@ProjectID <> 0 and (dbo.GLTran.ProjectID = Rtrim(@ProjectID))
or
and  (@ProjectID is not null and (dbo.GLTran.ProjectID = Rtrim(@ProjectID))
depending on the data type of @projectID
0
 
ralmadaCommented:
Sorry missed brackets there:

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

or

and  (@ProjectID is not null and (dbo.GLTran.ProjectID = Rtrim(@ProjectID)))  -- if @projectID is varchar
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
lulu50Author Commented:
Thank you
0
 
ralmadaCommented:
I'm sorry but TIM cotte is the same solution as mine. you should split points in this case
0
 
ralmadaCommented:
Would you mind reopen the question for further discussion?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.