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

Advertisements

Posted on December 1, 2010, in TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: