Concatinate user defined variable with select statement

[govind161986]

I am creating a stored procedure which has multiple joins. Now the problem is I want some joins to be executed for a given condition and some other join for other condition. For example

select c.cid, m.name, f.fname, l.lname
from tblcust c
left join tblmname m on c.id = m.cid
left join tblfname f on f.id = c.id
left join tbllname on l.id = c.id

What I want to do is

declare @val As varchar(10)
select @val = 'left join tblmname m on c.id = m.cid'

select c.cid, m.name, f.fname, l.lname
from tblcust c
left join tblmname m on c.id = m.cid
If condition is true then
+ @val +
left join tbllname on l.id = c.id

How to concatinate user defined variable in select clause?

Thanks in advance,

Govind

[ck9663]

Try this:

declare @val As varchar(10)

select c.cid, m.name, f.fname, l.lname
from tblcust c
left join tblmname m on @val = 1 and c.id = m.cid
left join tblfname f on @val = 2 and f.id = c.id
left join tbllname on @val = 3 and l.id = c.id

In the above sample, only one left join will actually be implemented at any time. If you want 2 or more, use OR ie (@val = 2 or @val = 3) and col1 = col2. Just a reminder, this is a left join, so everything in your "FROM TABLE" will still return regardless if there's match or not.

Happy Coding!!!

~~ CK

[govind161986]

This is what I want to do

select mf.mainid, mf.GAfrom tbl_Main mf
case mf.GA
When '1' THen Join tbl_MNotesn On mf.mainid = n.MNotesn_ID
End
where mf.cid = 1

But when I execute this query the sql returns an error "Incorrect Syntax near case"

[ck9663]

You can't do that. You can either use my technique that I recommended or use a dynamic query.

Good luck!!!

~~ CK