By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

Concatinate user defined variable with select statement

P: 21
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
Jan 27 '10 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
Try this:

Expand|Select|Wrap|Line Numbers
  1. declare @val As varchar(10)
  2.  
  3. select c.cid, m.name, f.fname, l.lname
  4. from tblcust c
  5. left join tblmname m on @val = 1 and c.id = m.cid
  6. left join tblfname f on @val = 2 and f.id = c.id
  7. left join tbllname on @val = 3 and l.id = c.id 
  8.  
  9.  
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
Jan 27 '10 #2

P: 21
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"
Jan 28 '10 #3

ck9663
Expert 2.5K+
P: 2,878
You can't do that. You can either use my technique that I recommended or use a dynamic query.

Good luck!!!

~~ CK
Jan 28 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.