Use Apply to call table-valued functions for each row in SQL Server 2005

server| function

Use The APPLY operator can call a table-valued function for each row returned by an external table expression that implements a query operation. The table-valued function is the right input, and the outer table expression is left-input. The resulting row is combined as the final output by evaluating the right input for each row in the left input. the list of columns generated by the APPLY operator is the set of columns in the left input, followed by the list of columns returned by the right input.

there are two forms of apply: CROSS apply and OUTER apply. CROSS APPLY returns only the rows in the external table that generate the result set through table-valued functions. OUTER APPLY returns both the row that generated the result set and the row that does not generate the result set, where the value in the column generated by the table-valued function is NULL.

--Create Test table
CREATE TABLE Test (OID Int,name char (), lead char (10))

--Insert a few lines of data inside
INSERT into test values (1, ' Test company ', ' 11 ')
INSERT into test values (1, ' Test company ', ' 12 ')
INSERT into test values (1, ' Test company ', ' 13 ')
INSERT into test values (1, ' Test company ', ' 14,15,16 ')


--Splitting String functions
Alter function Select_dempart_manager
(
@oid int,
@lead Char (10)
)
Returns @temp table (OID Int,lead char (10))
As
Begin
While CHARINDEX (', ', @lead) >1
Begin
INSERT into @temp values (@oid, left (@lead, charindex (', ', @lead)-1))
Set @lead =stuff (@lead, 1,charindex (', ', @lead), ')
End
INSERT into @temp values (@oid, @lead)
Return
End

--Use the Apply function call
Select A.oid,name,st.lead from Test a
Outer Apply Select_dempart_manager (a.oid,a.lead) as ST

--The result
--oid name Lead
--1 Testing Company 11
--1 Testing Company 12
--1 Testing Company 13
--1 Testing Company 14
--1 Testing Company 15
--1 Testing Company 16