T-SQL Table Function using declare statements

Question

I have the following SQL statement that I have been building up, which returns a range of values based on the order of values within a table for 'product data'

For example a product, TV, has a screen size and we want to query on TVs that have a screen between a specified range based on user configuration. So they may say, give me alternative TVs 3 sizes either side of 42".

This query does just that based on a specified product:

DECLARE @ProductType INT = (
                             SELECT p.prod_ptype_id
                                FROM product p
                                WHERE p.prod_id = @ProductId
                           )
DECLARE @AttributeId INT = (
                             SELECT at.aptype_attr_id
                                FROM Attribute_ProductType at
                                INNER JOIN Attribute a
                                    ON a.attr_id = at.aptype_attr_id
                                WHERE ISNULL(a.ReplacementTolerant, 0) = 1
                                    AND at.aptype_ptype_id = @ProductType
                           )
DECLARE @ToleranceRange INT = (
                                SELECT a.ToleranceRange
                                    FROM Attribute_ProductType at
                                    INNER JOIN Attribute a
                                        ON a.attr_id = at.aptype_attr_id
                                    WHERE ISNULL(a.ReplacementTolerant, 0) = 1
                                        AND at.aptype_ptype_id = @ProductType
                              )
DECLARE @AttributeValueIndex INT = (
                                     SELECT DISTINCT ( av.attrval_index )
                                        FROM Product_Attribute pa
                                        INNER JOIN AttributeValue av
                                            ON av.attrval_attr_id = pa.pattr_attr_id
                                        WHERE av.attrval_id = (
                                                                SELECT pattr_attrval_id
                                                                    FROM product_attribute pa3
                                                                    WHERE pa3.pattr_prod_id = @ProductId
                                                                        AND pa3.pattr_attr_id = @AttributeId
                                                              )
                                   );


WITH    ProductTypeAttributes
          AS (
               SELECT DISTINCT attrval_val
                   ,attrval_index
                   ,ROW_NUMBER() OVER ( ORDER BY attrval_index ) AS RowNumber
                FROM Product_Attribute pa
                INNER JOIN AttributeValue av
                    ON av.attrval_id = pa.pattr_attrval_id
                INNER JOIN Product p
                    ON p.prod_id = pa.pattr_prod_id
                WHERE pa.pattr_attr_id = @AttributeId
                    AND p.prod_ptype_id = @ProductType
                GROUP BY attrval_val
                   ,attrval_index
             )
    SELECT *
        FROM ProductTypeAttributes
        WHERE RowNumber >= (
                             (SELECT DISTINCT ( RowNumber )
                                FROM ProductTypeAttributes pta2
                                WHERE attrval_index = @AttributeValueIndex)
                           ) - @ToleranceRange
            AND ( RowNumber <= (
                                 SELECT DISTINCT ( RowNumber )
                                    FROM ProductTypeAttributes pta3
                                    WHERE attrval_index = @AttributeValueIndex
                               ) + @ToleranceRange )

This is all well and good, and works exactly as I need it to albeit it may need some optimizing, but for now I just want to be able to bolt it in where it's needed as a POC.

The obvious choice is to make this a table function - but I'm at a loss on what syntax to apply when I have these 'declare' statements.

I've had a look on MSDN and at the samples which show similar samples to what I think I need to achieve, but I can't fathom out how to use the declare statements in conjunction with a table function in this manner.

Can anyone please shed any light on this?

Thanks!

Answer 1

I can't quickly convert the entire query into an inline function with the time I'm willing to invest, especially without an understanding of what's in the tables and how they relate. However, I believe what I have below will at least replace your top 4 selects with something more reusable. I'm confident that using the same concepts you can merge the rest of the query into this function as well-- or perhaps a second function for more modularity. You might do better to optimize first before you try to convert it.

CREATE FUNCTION  GetProductInfo
(
    @ProductID int
)

RETURNS TABLE
AS
    RETURN
        SELECT
            p.prod_ptype_id AS ProductType,
            at.aptype_attr_id AS AttributeId,
            a.ToleranceRange AS ToleranceRange
            av.attrval_index AS AttributeValueIndex
        FROM        product p
        INNER JOIN  Attribute_ProductType at
        ON          at.aptype_ptype_id = p.prod_ptype_id
        INNER JOIN  Attribute a
        ON          a.attr_id = at.aptype_attr_id
        AND         a.ReplacementTolerant = 1
        -- This next section got a little confusing for me. Knowing more about what is in the various tables would help me figure out which tables
        --      might produce duplicate rows requiring the CROSS APPLY TOP 1 construct and which could be simple JOINs.
        -- Also, you are using Product_Attribute twice in the same query for @AttributeValueIndex in a confusing (to me) way, so I attempted to simplify here.
        CROSS APPLY (
                        SELECT
                            TOP 1 *
                        FROM        product_attribute pa_all
                        WHERE       pa_all.pattr_prod_id = p.prod_ptype_id
                        AND         pa_all.pattr_attr_id = at.aptype_attr_id
                    ) AS pa
        INNER JOIN  AttributeValue av
        ON          av.attrval_attr_id = pa.pattr_attr_id
        AND         av.attrval_id = pa.pattr_attrval_id
        WHERE       p.prod_id = @ProductId;

GO

DECLARE
    @ProductType INT
    @AttributeId INT
    @ToleranceRange INT,
    @AttributeValueIndex INT;
SELECT
    @ProductType = ProductType,
    @AttributeId = AttributeId,
    @ToleranceRange = ToleranceRange,
    @AttributeValueIndex = AttributeValueIndex
FROM        GetProductInfo
            (
                @ProductID
            );

Answer 2

Depsite my previous comment, rather than going for inline, I ended up using a multi-statement table function:

CREATE FUNCTION GetAttributeToleranceRange
(   
    @ProductId int
)
RETURNS @ProductTypeToleranceRange TABLE
(
    prod_type int,
    attr_id int,
    attrval_val nvarchar(max),
    attrval_index int, 
    RowNumber int
)
AS BEGIN

    DECLARE @ProductTypeAttributesTable TABLE
    (
        attrval_val nvarchar(max),
        attrval_index int, 
        RowNumber int
    )

    declare @ProductType int
    declare @AttributeId int 
    declare @ToleranceRange int 
    declare @AttributeValueIndex int 

    SELECT @ProductType = (SELECT p.prod_ptype_id FROM product p WHERE p.prod_id=@ProductId)

    SELECT @AttributeId = (SELECT at.aptype_attr_id FROM Attribute_ProductType at INNER JOIN Attribute a ON a.attr_id = at.aptype_attr_id WHERE ISNULL(a.ReplacementTolerant,0)=1 AND at.aptype_ptype_id = @ProductType)
    SELECT @ToleranceRange = (SELECT a.ToleranceRange FROM Attribute_ProductType at INNER JOIN Attribute a ON a.attr_id = at.aptype_attr_id WHERE ISNULL(a.ReplacementTolerant,0)=1 AND at.aptype_ptype_id = @ProductType)
    SELECT @AttributeValueIndex = (SELECT DISTINCT(av.attrval_index) FROM Product_Attribute pa INNER JOIN AttributeValue av ON av.attrval_attr_id=pa.pattr_attr_id WHERE av.attrval_id = (SELECT pattr_attrval_id FROM product_attribute pa3 WHERE pa3.pattr_prod_id=@ProductId and pa3.pattr_attr_id=@AttributeId));

    INSERT @ProductTypeAttributesTable SELECT DISTINCT attrval_val, attrval_index, ROW_NUMBER() OVER (ORDER BY attrval_index) As RowNumber
    FROM Product_Attribute pa 
    INNER JOIN AttributeValue av ON av.attrval_id = pa.pattr_attrval_id
    INNER JOIN Product p ON p.prod_id = pa.pattr_prod_id
    WHERE pa.pattr_attr_id = @AttributeId AND p.prod_ptype_id = @ProductType
    GROUP BY attrval_val, attrval_index;

    INSERT @ProductTypeToleranceRange SELECT @ProductType, @AttributeId, * FROM @ProductTypeAttributesTable WHERE RowNumber >= ((SELECT DISTINCT(RowNumber) FROM @ProductTypeAttributesTable pta2 WHERE attrval_index=@AttributeValueIndex)) - @ToleranceRange AND (RowNumber <= (SELECT DISTINCT(RowNumber) FROM @ProductTypeAttributesTable pta3 WHERE attrval_index=@AttributeValueIndex) + @ToleranceRange)
    RETURN
END

Again, it will benefit from some optimization but it does give me what I need using a single parameter whilst being usable where I need.