Is it possible to write a function in SQL Server 2000 accepting input parameter as a table?

Question

I have a sample data below where i want to manipulate the data and generate new database table.

I want the output as below image:

This is my query used to get the data:

    CREATE TABLE #tmp_vendorauth_HK ( 
                AuthMaterialKey varchar(30) not null, 
                CustomerNumber varchar(20) null
    ) ON [PRIMARY]


INSERT INTO #tmp_vendorauth_HK  (AuthMaterialKey, CustomerNumber)
SELECT DISTINCT basic_view.SalesOrganization + '@@' + basic_view.DistributionChannel + '@@' + basic_View.Material as AuthkeyMaterial, 
                ISNULL(RTRIM(ACG.CustomerNumber), '000001') + '@@' as CustomerNumber 
FROM            V_BASIC_MTR_ATTR_HK as basic_view
LEFT OUTER JOIN V_AUTH_CUST_GROUP ACG ON basic_view.Material = ACG.Material  
--ORDER BY 1 DESC

**TRUNCATE TABLE VendorAuth_group_HK


INSERT INTO VendorAuth_group_HK (AuthMaterialKey,CustomerNumber)
SELECT      AuthMaterialKey, substring(customernumbers, 1, len(customernumbers)-1)  
FROM        #tmp_vendorauth_HK a WITH(NOLOCK)
CROSS APPLY 
(
    SELECT  LTRIM(RTRIM(CustomerNumber)) + ',' 
    FROM    #tmp_vendorauth_HK TblskuDuplicate 
    WHERE   TblskuDuplicate.AuthMaterialKey= a.AuthMaterialKey

     FOR XML PATH('')
) AS t (customernumbers)**

drop table #tmp_vendorauth_HK

NOTE: I am using SQL Server 2000, therefore I cannot use CTE or CROSS APPLY features of T-SQL

Answer 1

HERE I GO with the simple solution to update my procedure Created a function to populate the details for me and used in my stored procedure.

Details of the function is as below

 CREATE FUNCTION dbo.fn_GET_CustomerNumbers
(
    @vcrMaterial VARCHAR(30)
)
RETURNS VARCHAR(8000)
AS 
BEGIN 

DECLARE             @vcrCustomerNumbers VARCHAR(8000)
SET         @vcrCustomerNumbers = ''

SELECT      @vcrCustomerNumbers = @vcrCustomerNumbers + ',' + CustomerNumber
FROM        tmp_vendorauth_HK 
WHERE       AuthMaterialkey = @vcrMaterial

SELECT      @vcrCustomerNumbers = STUFF(@vcrCustomerNumbers, 1,1, '')
RETURN      @vcrCustomerNumbers 

END
GO

The above function will return me the concatenated numbers which can be used in select statement as the another column can be your input parameter.