CODEWITHSUNDEEP
sql-servert-sqlinline-table-function
MilesToGoBeforeISleep
MilesToGoBeforeISleep

Reputation: 183

SQL Server 2012 Inline table-valued function returning empty rows

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.

Upvotes: 2

Views: 704

Answers (1)

JamieD77
JamieD77

Reputation: 13949

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

Upvotes: 4

Related Questions