0

I'm trying to create a function that returns the status of an Enquiry on a specified date. When I create this function with date as the only parameter it works, but modifying the function to also filter by Enquiry Key returns no rows. Please let me know if a dbfiddle is required to demonstrate.

CREATE FUNCTION [dbo].[fn_EnquiryStatusMonthly] (@Date date, @EnquiryKey varchar) RETURNS table AS RETURN (
SELECT
    TOP 1 ES_KEY as ESM_Link,
    E_STATUS_NAME as ESM_Stat,
    @Date as ESM_Date
FROM
    F_ESTATUS_CHANGE
INNER JOIN F_ENQ_STATUS ON
    E_STATUS_NO = ES_NEW_STAT
WHERE
    ES_DATE <= @Date
    AND ES_KEY = @EnquiryKey
ORDER BY
    ES_DATE DESC )

select EnquiryStatusMonthly.* from f_enquiry cross apply fn_EnquiryStatusMonthly(getdate(),E_Key) as EnquiryStatusMonthly
corky
  • 15
  • 6
  • If no rows are returns, this means no rows met the criteria the `WHERE` with the supplied parameters. `TOP` doesn't stop any rows being returned, only limits the (maximum) number. – Thom A Jul 31 '20 at 12:50
  • 2
    [Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). This is actaulyl just a typographical error, as I *assume* that `E_Key` isn't really a `varchar(1)`. – Thom A Jul 31 '20 at 12:52
  • `SELECT TOP 1 ES_KEY as ESM_Link, E_STATUS_NAME as ESM_Stat, ES_Date as ESM_Date FROM F_ESTATUS_CHANGE INNER JOIN F_ENQ_STATUS ON E_STATUS_NO = ES_NEW_STAT WHERE ES_DATE <= getdate() ORDER BY ES_DATE DESC` returns results. I don't understand what is causing there to be no matches with the supplied parameters. – corky Jul 31 '20 at 12:57
  • `@EnquiryKey varchar` = `@EnquiryKey varchar(1)`. We don't have any smaple data, but like I said, I doubt your column `E_Key` is a `varchar(1)` (if it is, why not use `char(1)`?). Read the linked article, it explains why not declaring your lengths, precisions and lengths, etc, is a bad idea. – Thom A Jul 31 '20 at 12:58
  • I didn't know not declaring a length made it default to varchar(1). Thanks. I changed it to varchar(15) and it's working! – corky Jul 31 '20 at 12:59
  • 1
    It depends on the context, @corky. Again, this is just one reason why you should **always** declare your lengths, etc. – Thom A Jul 31 '20 at 13:00

0 Answers0