Using query column for function parameter.

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 2000 Forums

SQL Server Development (2000)

Using query column for function parameter.

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-08 : 02:11:40

I have a user defined function which takes in an integer parameter, and works fine when I specify a value manually, but what I need to do is to use the function as part of an inner join, or something along those line and get the table results back depending on the row being queried.

Select
PIFC.PartnerName
From tblPartnerTickets PT
Inner Join (Select * From dbo.GetPartnerInformationFromContact(PT.OpenedBy)) PIFC
On PT.OpenedBy = PIFC.ContactID

This generates an error saying incorrect syntax near '.'. The period in question is PT.OpenedBy. If I remove the alias portion, it gives me an optimizer hint error or some such. Any idea how I can run my query in this way?

Thanks,
Steve

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-08 : 08:04:32

Here are a couple of ideas about your UDF. It is hard to know what is going on without the table structures or knowledge of what the UDF returns.

-- If the UDF returns a table, with columns ContactID, PartnerName, given the input parameter PT.OpenedBy. Does it return a single row or multiple rows, and do all rows fit the criteria OpenedBy=ContactID? This will make a huge difference in how the UDF is used.

It looks like the return value may always match ContactID. Why else would OpenedBy be an input parameter? I'm not sure if this is a strict rule or a false assumption.

Select PIFC.PartnerName
From tblPartnerTickets PT
Inner Join dbo.GetPartnerInformationFromContact(PT.OpenedBy) PIFC
On PT.OpenedBy = PIFC.ContactID

-- I'm not sure if doing to INNER JOIN a table that is parameterized with OpenedBy is valid.

Could a function was written to return a scalar: PartnerName?

SELECT GetPartnerName(OpenedBy)
FROM tblPartnerTickets

If neither of the above are possible, then:

SELECT
(Select X.PartnerName FROM dbo.GetPartnerInformationFromContact(PT.OpenedBy) X
WHERE X.ContactID=PT.OpenedBy) PartnerName
FROM tblPartnerTickets PT

Sam

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-08 : 12:44:06

You are correct in your first assumption, it returns one record determined by the passed in OpenedBy value. In actuality the SELECT is needing three columns from the table returned by the function. I was attempting to use the join since then the function would only need to be run one time.

I actually have a function named exactly that(GetPartnerName()), and it does precisely what it says, I was just trying to consolidate all the necessary information into one function returning a table if possible.

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-08 : 14:32:40

Could you post the function or is it a monster?

Sam

BTW

quote:
Select PIFC.PartnerName
From tblPartnerTickets PT
Inner Join dbo.GetPartnerInformationFromContact(PT.OpenedBy) PIFC
On PT.OpenedBy = PIFC.ContactID

- Did this select work or does it return an error?

Edited by - SamC on 06/08/2003 14:41:12

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-08 : 14:35:19

Here is is, very simple:


Create Function dbo.GetPartnerInformationFromContact
	(
	@ContactID int
	)
Returns table
As

Return Select 
	C.ContactID,
	C.ContactName,
	PT.PartnerType,
	PartnerName = dbo.GetPartnerName(C.PartnerTypeID, C.PartnerID)
From
	tblContacts C
	Inner Join tblPartnerTypes PT
		On C.PartnerTypeID = PT.PartnerTypeID
Where
	C.ContactID = @ContactID

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-08 : 14:48:12

It's simple enough that you could code it inline.

Does the form

Select PIFC.PartnerName
From tblPartnerTickets PT
Inner Join dbo.GetPartnerInformationFromContact(PT.OpenedBy) PIFC
On PT.OpenedBy = PIFC.ContactID

Return an error? (I'm not able to run QA from this client or I'd test it myself.)

Sam

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-08 : 14:52:01

That's one of the attempts I had run previously, and it gives the Incorrect syntax near '.' error message. I had tried wrapping it in a SELECT like in my first posting to see if I could possibly trick(longshot I know) SQL Server into working with it.

Steve

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-08 : 14:54:56

There may be a way to use the UDF to get what you want, and I'd like to know how myself.

In the short term. How about eliminating the UDF and coding the two inner joins to get it working?

Sam

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-08 : 14:56:54

That's what I've done for now, but this situation crops up somewhat regularly, so I'm holding out for the ability to do it with a function.

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-08 : 15:14:13

When the forum get's busy Monday morning, you might find a solution using a UDF.

A VIEW might be a real boon if you need the join a lot.

CREATE VIEW MyTrippleJoin AS
Select

C.ContactID,

C.ContactName,

PT.PartnerType,

PartnerName = dbo.GetPartnerName(C.PartnerTypeID, C.PartnerID)

From tblContacts C

Inner Join tblPartnerTypes PT On C.PartnerTypeID = PT.PartnerTypeID

GO

It's always there, and it's easy to use. What do you think?

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-08 : 15:18:46

I like the VIEW, but here's another angle using the UDF.

Modify the UDF so that if the parameter is NULL, it returns all the rows.

It's a simple change:

WHERE C.ContactID = @ContactID or @ContactID IS NULL

Then the JOIN you want can be coded as (and should work?)

Select PIFC.PartnerName
From tblPartnerTickets PT
Inner Join dbo.GetPartnerInformationFromContact(NULL) PIFC
On PT.OpenedBy = PIFC.ContactID

Does this do the trick?

Sam

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-08 : 15:19:01

I have considered the view approach, but it seems like it would be such a waste to have the server join for all contacts(in the tens of thousands), when only one specific contact is needed. I'll probably bump the question up on Monday to see if I get any hits.

Thanks for your help.
Steve

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-08 : 15:27:03

At that point, isn't it essentially the same thing as the view?

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-08 : 15:48:57

Views and Queries are a lot alike. With the UDF, if you provide a (not NULL) ContactID as a parameter, it'll return a specific row. This may be useful for your application. If you use a VIEW, you'll need a SELECT and a WHERE clause to get the same result.

I just read your concern about views creating an overhead. To get a specific row, the joins can be done in the UDF or in the VIEW for all the rows. It's just a matter of where. I don't believe there is any savings in either case.

Sam

Edited by - SamC on 06/08/2003 15:54:40

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-08 : 19:21:53

I am sorry to be the bearer of bad news but you cannot use a table valued UDF like that.

It will only accept variables and constants as arguments. No columns allowed.

And in other bad news, you have fallen into the UDF trap.

The function "GetPartnerName" is a scalar functions that does some simple qeury to get the parnter name, right?. It looks great in the execution plan, after all the join is gone and is replaced by a Compute which magically has 0 cost! Unfortunately it is all a big lie...

How could something which obviously has IO work to do, appear to not do any at all?

Try the view approach..

All I am saying, is give the optimiser a chance.

Apologies to John Lennon

DavidM

"SQL-3 is an abomination.."

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-08 : 19:30:10

Well of course the function does work, and the execution plan doesn't seem to dig down that far, but from a general standpoint, it seems like the function is still better. With the function I would be joining for only one specific row, whereas with the view, the joins are performed for every row, correct?

Of course it's all moot for my issue, but it still seems like a function is always better, optimizer or not. I'm not an SQL Server guru, not by a longshot, so if someone could explain how a view could be better in this situation, please explain how so.

Thanks,
Steve

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-09 : 04:04:03

quote:
but from a general standpoint, it seems like the function is still better. With the function I would be joining for only one specific row, whereas with the view, the joins are performed for every row, correct?

I didn't understand that at all.

Edited by - Arnold Fribble on 06/09/2003 04:05:00

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-09 : 11:33:42

Okay, I'll try again.

If you are joining on a table, and have a where clause. Aren't the where clause conditions applied first, and then the results of that joined together?

Select *
From tblContacts C
Inner Join tblContactTypes CT
On C.ContactTypeID = CT.ContactTypeID
Where C.ContactID = 550

So in the example above, rather than SQL Server performing the join for all rows in tblContacts, wouldn't it only perform the join for ContactID #550?

In a view, each time you call it, it performs it's query you've used to build it, so if the view used the above statement without the Where clause, wouldn't it perform the join for all rows in tblContacts?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-09 : 12:15:48

No, the optimizer will look into the view when it creates a plan.
But you were advocating functions.

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-06-09 : 12:47:51

I was advocating functions in that this block of code would be contained within it so that it could easily be reused:

Select *
From tblContacts C
Inner Join tblContactTypes CT
On C.ContactTypeID = CT.ContactTypeID
Where C.ContactID = @ContactID

I just used that for the example without the function wrapping in an attempt to illustrate my question about performance between a view, and an already constrained query.

Steve

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-09 : 12:55:21

In your View example, you will find that if you:

SELECT * from
AnyView
WHERE .....

The entire view is NOT evaluated --- this is key to understand. SQL Server will parse your where clause and apply the filter right down to the source tables, if it can.

Thus,

Select *
From ContactsView
WHERE
ContactID = 550

Should be exactly as efficient as:

Select *
From tblContacts C
Inner Join tblContactTypes CT
On C.ContactTypeID = CT.ContactTypeID
Where C.ContactID = 550

in your example.

- Jeff

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources