UDF Parameter passing in Query
-
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 totalFrom App_position b
Where b.portID = @PortID
Select (select Sum( a.total ) from dbo.TestSharesTable1( 1000, @Count ) a ) as totalFrom App_position b
Where b.portID = @PortID
Select (select Sum( a.total ) from dbo.TestSharesTable1( b.InvestID, @Count ) a ) as totalFrom App_position b
Where b.portID = @PortID
-- Incorrect syntax near '.' --
-
Could you post the function?
It's pretty hard to see whats happening without the source.
/Kenneth
-
You can only do this if TestSharesTable is a scalar function.
-
You can try this... (if your function returns "total")
Select Sum( dbo.TestSharesTable1( b.InvestID, @Count ) 
as totalFrom App_position b
Where b.portID = @PortID
-
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply