UDF Parameter passing in Query

Question

Post reply

UDF Parameter passing in Query

Billpl

SSC-Addicted

Points: 430
More actions
June 9, 2004 at 11:18 pm

#359836

The first 2 queries work (obviously) but the third will not accept [b.investID] as a parameter.
Anyone know the reason?
Can't pass a parameter through a query to a UDF that represents a Table?
That doesn't sound right.
Select (select Sum( a.lastprice ) from app_investItem a where b.investID = a.InvestID ) as total
From App_position b
Where b.portID = @PortID
Select (select Sum( a.total ) from dbo.TestSharesTable1( 1000, @Count ) a ) as total
From App_position b
Where b.portID = @PortID
Select (select Sum( a.total ) from dbo.TestSharesTable1( b.InvestID, @Count ) a ) as total
From App_position b
Where b.portID = @PortID
-- Incorrect syntax near '.' --

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Could you post the function?

It's pretty hard to see whats happening without the source.

/Kenneth

wz700 SSCertifiable Points: 7133 More actions · Answer 2

You can only do this if TestSharesTable is a scalar function.

Bavineni SSC Enthusiast Points: 147 More actions · Answer 3

You can try this... (if your function returns "total")

Select Sum( dbo.TestSharesTable1( b.InvestID, @Count )&nbsp as total

From App_position b

Where b.portID = @PortID

Billpl SSC-Addicted Points: 430 More actions · Answer 4

Thanks guys.

AFAICS It appears that wz700 is correct. It's not allowed because it's not scalar. I do wonder why. Usually there's some kind of logical reason.

What I'm trying to do here is clean up a rather complex project. There are lots of repetitive sub-queries and would be quite nice to compact them into a handful UDF's and pass them around, but if it can't handle parameters?...well what can I say.

Bavineni's suggestion does handle most of my situations, in fact, I have already used it in several places, but just not as clean as the table method.