How to pass parameters into inline UDF from a sub query

Question

How to pass parameters into inline UDF from a sub query

muntasiriub

SSCommitted

Points: 1810
More actions
January 19, 2004 at 10:52 pm

#106858

Suppose I have an inline UDF as follows:

“create function f_Fglastpurchaseinfo(@m_locationid varchar(15),@m_itemcode varchar(15))
returns table
as return
-----------
--------“
Now, I would like to pass these parameters into “f_Fglastpurchaseinfo” from a sub query as follows:

“select mytable.name1,mytable.name2, myudf.quantity,myudf.price ……..
from tbltable as mytable left join f_Fglastpurchaseinfo(mytable.locationid,mytable.itemcode)
as myudf on…….”

But I cannot pass mytable.locationid or mytable.itemcode , it shows as error message.
Can u help me on that?

..Better Than Before...

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

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

What is the error? On what are you joining?

An inline table function doesn't return field names or field information. You may need to do a Multi-statement table-valued function so that you can specify field names.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

muntasiriub SSCommitted Points: 1810 More actions · Answer 2

Rawhide

i think u have'nt got my point..

i have not any problem in returning part but problem in param part

got it?

..Better Than Before...

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Perhaps I would understand better if you told me what the error is. I'm good, but I'm not psychic.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

ThomasH SSCertifiable Points: 7437 More actions · Answer 4

In a prior post I once wrote:

@ http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=19016#bm90510

I was wanting to do the same thing some time ago... I realized that Table Function parameters can NOT be values from a "Joined" table. I think is is because the Table Function is meant to return a complete recordset for every call, and a "Join" wants one record set at a time. What I did, was have the Table Function return ALL the records needed for the entire Select. In other words, Select from Progress_Notes all the required Date_ values, or Min(), Max() range of Date_, placed into @Vars, then use the @Vars as the parameters to the Table Function. The Table Function should include the column with Date_ values so you can use it as part of Join. I'm hoping like you are, that someone comes back and tells us it can be done in an "inline" manner.

Once you understand the BITs, all the pieces come together