CODEWITHSUNDEEP
sqlsql-serversql-server-2008
StatsViaCsh
StatsViaCsh

Reputation: 2640

SQL server udf not working

I'm now all day on a fairly simple udf. It's below. When I paste the select statement into a query, it runs as expected... when I execute the entire function, I get "0" every time. As you know there aren't a ton of debugging options, so it's hard to see what value are/ aren't being set as it executes. The basic purpose of it is to make sure stock data exists in a daily pricing table. So I can check by how many days' data I'm checking for, the ticker, and the latest trading date to check. A subquery gets me the correct trading dates, and I use "IN" to pull data out of the pricing and vol table... if the count of what comes back is less than the number of days I'm checking, no good. If it does, we're in business. Any help would be great, I'm a newb that is punting at this point:

ALTER FUNCTION dbo.PricingVolDataAvailableToDateProvided
    (@Ticker char,
     @StartDate DATE,
     @NumberOfDaysBack int)
    RETURNS bit
AS
    BEGIN

    DECLARE @Result bit
    DECLARE @RecordCount int

SET @RecordCount = (
    SELECT COUNT(TradeDate) AS Expr1
    FROM   (SELECT TOP (100) PERCENT TradeDate
            FROM   tblDailyPricingAndVol
            WHERE  ( Symbol = @Ticker )
                   AND ( TradeDate IN (SELECT TOP (@NumberOfDaysBack)
                                      CAST(TradingDate AS DATE) AS Expr1
                                       FROM   tblTradingDays
                                       WHERE  ( TradingDate <= @StartDate )
                                       ORDER  BY TradingDate DESC) )
            ORDER  BY TradeDate DESC) AS TempTable ) 

    IF @RecordCount = @NumberOfDaysBack
        SET @Result = 1
    ELSE
        SET @Result = 0

    RETURN @Result

    END

Upvotes: 1

Views: 103

Answers (1)

Martin Smith
Martin Smith

Reputation: 453707

@Ticker char seems suspect.

If you don't declare a length in the parameter definition it defaults to char(1) so quite likely your passed in tickers are being silently truncated - hence no matches.

SELECT TOP (100) PERCENT TradeDate ... ORDER  BY TradeDate DESC

in the derived table is pointless but won't affect the result.

Upvotes: 1

Related Questions