Functions/SP aware that were called using a join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Functions/SP aware that were called using a join?

Author

Topic

ishaybas
Starting Member

5 Posts

Posted - 2009-12-14 : 01:12:33

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!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-14 : 01:17:00

I think it can't possible.. Let us wait for others! ;)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-14 : 01:26:58

Hi

You mean Table-Valued User-Defined Function...

-------------------------
R...

ishaybas
Starting Member

5 Posts

Posted - 2009-12-14 : 01:31:35

Yes, either a UDF or SP.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-14 : 01:50:06

Hi

Try APPLY Clause...
http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

-------------------------
R...

ishaybas
Starting Member

5 Posts

Posted - 2009-12-14 : 12:24:21

Hey,

Thanks for the tip.

I understand APPLY enables me to add the results of a function to a table.
However, what I need is a way inside the function to know whether its outcome will be joined with another specific table or no.

Sadly, passing a parameter to this function will require me to find and change all the locations it is called from.

Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 13:53:40

Can you explain why you wanna do this? May be there is a better way of dealing this altogether.

quote:
Originally posted by ishaybas

Hey,

Thanks for the tip.

I understand APPLY enables me to add the results of a function to a table.
However, what I need is a way inside the function to know whether its outcome will be joined with another specific table or no.

Sadly, passing a parameter to this function will require me to find and change all the locations it is called from.

Thanks!

ishaybas
Starting Member

5 Posts

Posted - 2009-12-15 : 16:57:57

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources