alternative to CROSS APPLY

Question

I have a stored procedure that I use to squeeze multiple rows of data into one variable, like so:

DECLARE @Description VARCHAR(200);

SELECT @Description = COALESCE(@Description) + ', ', '') + [Description]
FROM dbo.DrugDescriptionAndImprints
WHERE NDC = @NDC;

My problem is, I recently had a need to accomplish this for multiple NDCs simultaneously for generating report data.

My current solution is to use CROSS APPLY with a table-valued function, but that is less than optimal due to the performance hit. I would like to be able to "flatten" this data in a view or something, but am unable to use variables in a view. Is there a way to accomplish this without using CROSS APPLY and table-valued functions?

Here is the table-valued function for reference:

CREATE FUNCTION [dbo].[fnFlatDescriptionByNdc]
(
    @NDC VARCHAR(11)
)
RETURNS 
@ret TABLE 
(
    [Description] VARCHAR(200)
)
AS
BEGIN
    DECLARE @Description VARCHAR(200)
    DECLARE @Imprint1 VARCHAR(40)
    DECLARE @Imprint2 VARCHAR(40)

    SELECT @Description = Coalesce(@Description + ', ', '') + [Description]
    FROM dbo.DrugDescriptionAndImprints WHERE NDC = @NDC

    SELECT @Imprint1 = COALESCE(Imprint1, '')
    FROM dbo.DrugDescriptionAndImprints WHERE NDC = @NDC 
    ORDER BY Imprint1 DESC

    SELECT @Imprint2 = COALESCE(Imprint2, '')
    FROM dbo.DrugDescriptionAndImprints WHERE NDC = @NDC 
    ORDER BY Imprint2 DESC

    INSERT INTO @ret ([Description])
    SELECT @Description + CASE WHEN @Imprint1 <> '' OR @Imprint2 <> '' THEN ' (' +
        CASE WHEN @Imprint1 <> '' THEN @Imprint1 + 
            CASE WHEN @Imprint2 <> '' THEN ' / ' + @Imprint2
            ELSE ''
            END
        ELSE @Imprint2 END + ')' ELSE '' END

    RETURN 
END

I am working with SQL Server 2008 R2.

EDIT Here is some sample data to show what I am working with:

NDC              ProdDescAbbr           Description        Imprint1      Imprint2
00005250033      FIBERCON   TAB 625MG   film-coated        LL            F 1
00005250033      FIBERCON   TAB 625MG   tan                LL            F 1
00005250033      FIBERCON   TAB 625MG   scored             LL            F 1
00005250033      FIBERCON   TAB 625MG   oblong             LL            F 1

Answer 1

Your explanation of what you're doing now and what you want to do are a bit confusing but I will address what I see.

I think you are saying you do not want to use a user defined function, which is good. There are no performance gains from UDF's and, in fact, you will see performance losses if you use anything but an inline table function, which you do not have. The only real advantage to UDF's is code-reuse, which can be great for inline table functions that have been optimized and are used all over the place. However, let's assume this will not fall into that category. If you do need to implement a UDF, please invest the time to understand how to perform operations in a set-based manner so you can use an inline table function. The code I provide below could be easily modified to work in an inline table function.

You made reference to CROSS APPLY, which you're not using in any of the code you supplied. I am not sure if you are meaning that you are using a CROSS APPLY to join to the function. In any case, my understanding is that you want to move away from the function to be able to get multiple records at the same time. I do not know your data, but I am guessing that is a good call and you can get better performance by doing this work on multiple NDC's at the same time.

I created some test data (that is not just one NDC) and changed things a bit to be able to verify how everything works:

DECLARE @myTable TABLE
    ( tableId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , NDC VARCHAR(11) NOT NULL 
    , ProdDescAbbr VARCHAR(30) NOT NULL 
    , [Description] VARCHAR(30) NOT NULL
    , Imprint1 VARCHAR(5) NOT NULL
    , Imprint2 VARCHAR(5) NOT NULL );

INSERT INTO @myTable VALUES
    ('00005250033', 'FIBERCON   TAB 625MG', 'film-coated', 'LL', 'F 1' ),
    ('00005250033', 'FIBERCON   TAB 625MG', 'tan', 'LL', 'F 1' ),
    ('00005250033', 'FIBERCON   TAB 625MG', 'scored', 'LQ', 'F 1' ),
    ('00005250033', 'FIBERCON   TAB 625MG', 'oblong', 'LL', 'F 2' ),
    ('00005250034', 'FIBERCON   TAB 625MG', 'short', '', '' ),
    ('00005250034', 'FIBERCON   TAB 625MG', 'green', '', '' ),
    ('00005250035', 'FIBERCON   TAB 625MG', 'open', '', '' ),
    ('00005250035', 'FIBERCON   TAB 625MG', 'yes', '', '' );

Hopefully you have some primary key on the table ... I took the liberty of creating one. Also, it may be a bit odd to take the imprints by the max value instead of having some kind of lookup table with an adjustable precedent value but we will not spend time evaluating the structure since that was not what the question was really about.

In this example (since I do not have an example query where you are consuming the results of your function) I am using the cte 'cteTemp' to represent whatever it would be in the parent query that defines which NDC's you want to know about and joining that to the source data ... @myTable in my example and dbo.DrugDescriptionAndImprints in your system:

WITH cteTemp AS
    (
    SELECT *
    FROM @myTable
    WHERE NDC IN ('00005250033', '00005250035')
    )
, cteImprints AS
    (
    SELECT  cte.NDC
        ,   Imprint1 = MAX(cte.Imprint1)
        ,   Imprint2 = MAX(cte.Imprint2)
    FROM cteTemp AS cte
    GROUP BY cte.NDC
    )
, cteFinal AS
    (
    SELECT  cte.NDC
        ,   Descriptions = STUFF(CONVERT(VARCHAR(1000), 
            (SELECT ',' + sq.[Description] 
            FROM cteTemp AS sq 
            WHERE sq.NDC = cte.NDC 
            ORDER BY sq.[Description] 
            FOR XML PATH (''))), 1, 1, '')
        ,   cte.Imprint1
        ,   cte.Imprint2
    FROM cteImprints AS cte
    )
SELECT  cte.NDC
    ,   concatenatedString = 
        cte.Descriptions + CASE WHEN cte.Imprint1 <> '' OR cte.Imprint2 <> '' THEN ' (' +
        CASE WHEN cte.Imprint1 <> '' THEN cte.Imprint1 + 
            CASE WHEN cte.Imprint2 <> '' THEN ' / ' + cte.Imprint2
            ELSE ''
            END
        ELSE cte.Imprint2 END + ')' ELSE '' END
FROM cteFinal AS cte;

There are some potential performance issues. Ideally we would only read from the source table (@myTable / dbo.DrugDescriptionAndImprints) once and do everything from there. The sticking point here is that we can get the max imprints in one query but I don't think we can avoid needing a separate pass to get the description concatenation for each NDC that we want. You could use a table variable or temp table to avoid going back to the source, but normally that is worse than accepting the return trips. But you would have to test this on your system and evaluate your execution plans to determine whether or not it meets your performance criteria.