SQL Server 2012 Inline table-valued function returning empty rows

Question

I have a table called TaxLots that was created with these columns:

CREATE TABLE Portfolio.TaxLots
(
Ticker varchar(22) NOT NULL,
SecurityDescription varchar(50) NOT NULL,
Class varchar(15) NULL,
Ccy varchar(5) NULL,
LSPosition char(3) NULL,
Date date NULL,
Quantity int NULL,
LocAvgCost decimal(8,3) NULL,
LocTaxCostBasis int NULL,
LocMktVal int NULL,
BaseAvgCost decimal(8,3) NULL,
BaseTaxCostBasis int NULL,
BaseMktVal int NULL,
BaseUNRL int NULL,
DateCreated DATE NOT NULL DEFAULT Cast(GetDate() as Date)
)
GO

I want to create a function that will pull up some of these columns for a specific Ticker (which would be my parameter) for the last DateCreated. I created the following function:

USE FundDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetTaxLots
(   
@Ticker varchar
)
RETURNS TABLE 
AS
RETURN 
(
SELECT
    TL.Ticker,
    TL.SecurityDescription,
    TL.LSPosition,
    TL.Date,
    TL.Quantity,
    TL.LocAvgCost,
    TL.LocTaxCostBasis,
    TL.BaseAvgCost,
    TL.BaseTaxCostBasis,
    TL.DateCreated,
    (SELECT SUM(Quantity) 
    FROM
    Portfolio.TaxLots) AS TotalQuantity
FROM
    Portfolio.TaxLots TL
WHERE
    TL.DateCreated= (SELECT Max(TL.DateCreated) FROM Portfolio.TaxLots TL) 
    AND TL.Ticker = @Ticker
)

I tested the SELECT statement by itself with a Ticker hard-coded and it gives my the desired result but when I try to use the function using a SELECT statement like this:

USE FundDB
SELECT *
FROM dbo.GetTaxLots('MSFT')
GO

I get empty rows. Very confused. I'm pretty new at this so I feel like I'm missing something obvious.

JamieD77 · Accepted Answer · 2016-08-18T16:19:51.573

4

You need to set the correct size of your @Ticker variable

CREATE FUNCTION dbo.GetTaxLots
(   
    @Ticker varchar(22)
)

if you just use @Ticker varchar the variable will only be 1 character long, so when you call SELECT * FROM dbo.GetTaxLots('MSFT') the value of @Ticker in your function will be just M

As a side note.. it might help performance to combine your 2 sub queries into one derived table.

SELECT  TL.Ticker,
        TL.SecurityDescription,
        TL.LSPosition,
        TL.Date,
        TL.Quantity,
        TL.LocAvgCost,
        TL.LocTaxCostBasis,
        TL.BaseAvgCost,
        TL.BaseTaxCostBasis,
        TL.DateCreated,
        IJ.TotalQuantity
FROM    Portfolio.TaxLots TL
        INNER JOIN (SELECT  SUM(Quantity) TotalQuantity, 
                            MAX(DateCreated) MaxCreateDate 
                    FROM    Portfolio.TaxLots ) IJ ON TL.DateCreated = IJ.MaxCreateDate
WHERE   TL.Ticker = @Ticker

edited Aug 18 '16 at 16:19

answered Aug 18 '16 at 16:08

JamieD77

13,796
1
17
27

OMG... so simple. Thank you! – MilesToGoBeforeISleep Aug 18 '16 at 16:14
Thanks again Jamie for the side-note. Can you please tell me how a derived table improves performance over sub queries? – MilesToGoBeforeISleep Aug 18 '16 at 16:34
1

since you're not filtering your subqueries they will most likely be fast and pulled from cache for each row so performance may not increase tremendously, but it's best to avoid subqueries in your select or where if possible – JamieD77 Aug 18 '16 at 16:55

SQL Server 2012 Inline table-valued function returning empty rows

1 Answers1