Wednesday, December 1, 2010

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

No comments:

Post a Comment

All suggestions are welcome