Sql Server: Table Valued Function

Table valued function is bit different from commonly used scalar valued function, where it returns a single value. But table valued function returns table (in form of columns and rows). Usage of table valued function is bit different from that of scalar valued function. Let create a table valued function and check its usage.

During development we commonly need our comma separated string values in a table format to use in different type of queries.

Our input looks like  '45,65,748,ABC001,XYZ05'
And we need output like following

To achieve our goal lets create a table valued function

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnc_GetValuesInColumnFromCSV]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

BEGIN

execute dbo.sp_executesql @statement = N'-- =============================================     

-- Author:  AASIM ABDULLAH     

-- Description: TO GET VALUE IN TABLE FORMAT FROM CSV INPUT     

-- =============================================     

CREATE FUNCTION [dbo].[fnc_GetValuesInColumnFromCSV]      

(      

@csv NVARCHAR(4000)      

)     

RETURNS @result_table TABLE      

(      

 value NVARCHAR(250) NULL     

)     

AS      

BEGIN     

 -- Declare the return variable here     

    DECLARE @MyTempTable  TABLE ( ID INT )     

     

 -- Add the T-SQL statements to compute the return value here     

    IF Len(@csv) > 0      

        BEGIN       

            DECLARE @i INT       

            SELECT  @i = 1       

       

            WHILE ( @i <= LEN(@csv) + 2 )       

                BEGIN       

                    INSERT  INTO @MyTempTable     

                            SELECT  @i       

                    SET @i = @i + 1       

                END       

          

            DECLARE @IDs nvarchar(4000)       

            SET @IDs = @csv       

       

----       

       

            INSERT  INTO @result_table ( value )     

                    SELECT  NullIf(SubString('','' + @IDs + '','', ID,     

                                             CharIndex('','', '','' + @IDs + '','',     

                                                       ID) - ID), '''') AS value     

                    FROM    @MyTempTable AS P     

                    WHERE   ID <= Len('','' + @IDs + '','')     

                            AND SubString('','' + @IDs + '','', ID - 1, 1) = '',''     

                            AND CharIndex('','', '','' + @IDs + '','', ID) - ID > 0         

       

        END     

 -- Return the result     

    RETURN     

     

   END '

END

GO

How to use it:

1.        SELECT  *

FROM    dbo.fnc_GetValuesInColumnFromCSV('45,65,748,ABC001,XYZ05')

2.  Use AdventureWorks

SELECT  *

fROM    Purchasing.Vendor

WHERE   AccountNumber IN (

        SELECT  [value]

        FROM    dbo.fnc_GetValuesInColumnFromCSV('PREMIER0001,COMFORT0001,

METROSP0001,GREENLA0001,MOUNTAIN0001') )

3.  SELECT  *

FROM    Purchasing.Vendor

INNER JOIN dbo.fnc_GetValuesInColumnFromCSV('PREMIER0001,COMFORT0001,METROSP0001,GREENLA0001,MOUNTAIN0001')

                    AS CSV ON Purchasing.Vendor.AccountNumber = CSV.value