How to dynamically decide what tables to join in SQL Server stored procedure

Question

I have a table tbl_Info:

InfoId
Text
PrivacyTypeId
UserName
TypeId
IsInfo
InfoItemId

Example of data:

1|Some text...|1|userX|1|False|NULL
2|New job created|1|system|3|True|765
3|Image commented|1|system|4|True|457

In my application I get all values from this table and I display them as a list.

Application users can click on each of this items and they should be redirected somewhere in application.

I have a few tables in database that is bound for this table but this connection is very dynamic. For example this tables:

tbl_Jobs{JobId, Title etc.}, tbl_Articles{ArticleId, Title etc.}

Here is the problem:
Before I added IsInfo and InfoItemId I have only one type where users enter this infos and all I need is to get info columns + user full name:

select i.*, p.FirstName + ' ' + p.LastName as Author 
from tbl_Info i 
left join tbl_Profiles p on i.UserName = p.UserName
where i.PrivacyTypeId = 1

Now I need to change this Author column with name of Title and this values should be: Full name or Job Title or Article Title etc.

I don't know is it possible to make select procedure so it can based on IsInfo and TypeId get item title from another table with ID of InfoItemId.

So something like: If IsInfo = True. Get typeId. And based on this typeId join to some table and get some column from it.

IF typeId = 2 ... join tbl_Jobs on ...
IF typeId = 3 ... join tbl_Articles on ...

I'm sorry for a little longer post. But I can't find solution for this and I need to explain problem in a little more detail.

Answer 1

you could do something like this:

SELECT
 CASE typeID
   WHEN 1 THEN tbl_Profiles.FirstName + ' ' + tbl_Profiles.LastName
   WHEN 2 THEN ...
   WHEN 3 THEN ...
 END AS Title
FROM tbl_Info 
LEFT OUTER JOIN tbl_Profiles ON 
   tbl_Info.UserName = tbl_Profiles.UserName
   AND typeID = 1
LEFT OUTER JOIN tbl_Jobs ON
   ....
   AND typeID = 2
LEFT OUTER JOIN tbl_Articles ON
   ....
   AND typeID = 3

Answer 2

It's possible that I have missed something however on first inspection it would appear that you could address your need to have two code paths within a given stored procedure by using a simple IF statement.

IF (ConditionIsTrue)
BEGIN
      -- First form of select statement
END
ELSE
BEGIN
     -- Second form of select statement
END

Answer 3

If you need to display the data in one big list, you can use an union to tie them together:

SELECT i.*, p.FirstName + ' ' + p.LastName as Author 
  FROM tbl_Info i
    INNER JOIN tbl_Profiles p ON i.UserName = p.UserName
    WHERE i.PrivacyTypeID=1
UNION ALL
SELECT i.*, j.Title
  FROM tbl_Info i
     INNER JOIN tbl_Jobs j ON ...
     WHERE i.PrivacyTypeID=2
...

Otherwise, if you really just want to display different things at a time (i.e. just profiles, just jobs, etc, then go with John Sansom's answer, wrapping it with IF statements