Can a Function/SP be aware whether its results be joined with a table?

[ishaybas]

Hi,

Is there any way for a function or a stored procedure to be aware of whether its results will be joined with another table in the calling query?

For example, assume one query calls:

Select * from MyFunction

while another query does:

Select * from MyFunction LEFT JOIN MyTable on ...

I would like that MyFunction will return different results in both cases.

Is there a way to accomplish this from within the function? (without passing a special parameter ..)

Thank you!

 

[bborissov]

AFAIK, no.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[danvlas]

I think stored procedures cannot be joined with other sps/tables/views/functions.

You cannot tell a function to return different results based only on 'external' joins.

Just out of curiosity: why would you want such a feature?


Daniel Vlas
Systems Consultant

 

[markros]

You may look into CONTEXT_INFO() function - not sure it may be applied to the function, though.

http://msdn.microsoft.com/en-us/library/ms180125.aspx

Can you explain more about your scenario - it sounds like quite a strange request...

 

[ishaybas]

Certainly,

please assume the following:

1. accounts table: columns: account_number, creation_date, dealer_code
2. dealers table: columns: dealer_code, address

there are many queries using these tables.
some just query the accounts, some just the dealers, and some join them
using the dealer_code, and plotting:

account_number,dealer_code,address,creation_date
1,100,New York,Aug09
2,100,New York,Nov09
3,69,Washington,Jan09
4,100,New York,Mar09
5,71,Richmond,Aug09
....

a requirement came in to keep track of the different addresses that dealers change, and
whenever a query to join the tables is made, the dealer address shown, should be relevant
to the point in time that the account was created.

HOWEVER, no queries or reports that are using the tables should be changed.
that is, if I have a report that does:

select * from accounts left join dealers on accounts.dealer_code = dealers.dealer_code

it should stay exactly the same, just return the correct address per the creation date
of the account.

I thought of doing the following, create a history tracking table:

dealer_code, address, change_date

and create a view or function for either the accounts or dealers table (by using the same name as the table, and renaming the table, this way
no reports needs to be changed) that will do this trick.

The view will merely join the history tracking table, and select the correct address based on the closest oldest
change date to account creation date.
meaning if a dealer changed address in Jun09, and an account was created in Jul09, then the Jul09 account will get the new address,
while all accounts before Jun09 will get the old address.

so far so easy. THE PROBLEM is,
if I create this view instead of the accounts table, that the view will return the correct address,
I will have to modify all reports as they manually join the dealers table and
take the address from the dealers table.

If I create this view instead of the dealers table, how do I program it that in case it is joined
with an account, it checks for the correct address, and in case it is not joined with the accounts
table, simply returns the newest address?

I could have the dealers table always returned a list of all accounts, the dealer_no and the corresponding address,
however, in case the report wishes to query the dealers table, just to get the dealers list, it will have lots of duplicates.

or any other solution you can think of, will be great.

Thank you!

 

[RyanEK]

So you want to get the relevant dealer address for each account? This would be relatively straight forward if you could update the queries in your report but as I understand it this is your restriction?

I think you're on the right track. I would create a table populated with the ONE correct dealer address for each account and have the "Dealer" view read from this instead. The table can be populated/updated via a stored procedure and called any time an address is updated.

 

[ishaybas]

Yep, that part is easy.

Trouble is my restriction is on limiting the queries and reports using those tables,
and that the dealers table must remain in a condition to return just a list of dealers and their current addresses, regardless of accounts, and with no duplicates ..

 

[RyanEK]

Is populating this proxy table the part you're having difficulty with? You could do something like the following where each account is linked to the latest address. There should be no duplicates

select a.account_number, a.dealer_code, t.address, t.change_date
from account a
join (
    select dealer_code, address, change_date,
	RANK() over (partition by dealer_code order by change_date desc) as dl_rank
    from dealers
) as t on t.dealer_code = a.dealer_code and t.dl_rank = 1
order by account_number

 

[ishaybas]

Hy Ryan,

Actually populating the proxy table is not the main difficulty,
however the solution you proposed for that task is certainly impressive.

The main difficulty is that some queries call the dealer table with no relation to any accounts, and expect a distinct list of dealers in return. (one record per dealer).
If my function were to join with the accounts table like mentioned above, the returned dealer list will have lots of duplicate dealers because each record would correspond to an account.