passing table orcolumns as input parameters to a UDF in sqlserver2005

[techytechy]

Hi Guys,
I m using sqlserver2005.
Need to know the syntax to pass a table or table variable or temp table or column to a User defined function. I am not able to find a document that says we could. One document says we got to use cross apply?

Thanks

 

[markros]

If you have a table-valued UDF then

select myTable.fields, F.Field1, F.Field2 from myTable CROSS APPLY dbo.myUDF(myTable.SomeField, myTable.AnotherField) F

Brad Schulz wrote great blogs on CROSS APPLY operator, let me find it for you.

 

[markros]

It is probably this blog I meant

http://bradsruminations.blogspot.com/2009/10/un-making-list-or-shredding-of-evidence.html

I saved my favorites now on Delicios website - quite convinient to search.

 

[techytechy]

Thanks Markros!

Its not working in my case. May be the definition in the myUDF is not compatible to take in the params. Its a table-valued function.
Below is my definition:

CREATE FUNCTION [fn_test] (
@param1 VARCHAR(128)
,@param2 VARCHAR(128) )
RETURNS @test TABLE (
col1 varchar(100)
,col2 varchar(100)
)

How the @param1 to be declared to take in the columns?
And I am forced to do the Cross Apply? coz what all I want to do is
select * from fn_test(table.col1, table.col2)

Thanks

 

[markros]

Yes, you do need to use cross apply and do you want to use two tables?

select T1.Col1, T2.Col2, F.Col1, F.Col2 from Table1 T1 inner join Table2 T2 on T1.Pk = T2.Fk cross apply dbo.fn_test (T1.Col1, T2.Col2) F

 

[techytechy]

I tried many times as syntax below , adding schema names etc but sql server 2005 throws error:

Incorrect syntax near '.'.

whats wrong with this syntax?

select * from Table1 as T1 cross apply dbo.fn_test (T1.Col1, T1.Col2) F


thanks

 

[markros]

Worked fine for me

select * from test as T cross apply dbo.fnSplit(T.Name, '1') F

What is the compatibility level of your database?