Is it an SQL SERVER bug?

Question

I cant seem to figure out what is going on with this SQL scalar function. I am wondering if this is some kind of bug that I've fallen upon because the select statement is returning NULL in the function while it works perfectly well outside the function or when the DESC clause is omitted.

ALTER FUNCTION [dbo].[getInvoiceNumbers]
(

@vatable bit

)
RETURNS nvarchar(20)
AS
BEGIN

DECLARE @latestNonVATInvNumber nvarchar(20), 
        @latestVATInvNumber nvarchar(20), @invoiceNumber nvarchar(20)

if @vatable = 'true'
    begin    
        select TOP 1 @latestVATInvNumber = invoiceNumber from dbo.Invoices where vatable = 'true' ORDER BY invoiceID desc 

        /* remove 'B' from invoice number and convert to int from string */

        set @latestVATInvNumber = cast(substring(@latestVATInvNumber,2,len(@latestVATInvNumber)) as int) + 1

        /* Add 'B' to receipt number with leading zeros so that it is 7 digits including 'B' */

        set @latestVATInvNumber  = 'B' + RIGHT('000000'+ CAST(@latestVATInvNumber AS VARCHAR(6)),6)

        set @invoiceNumber = @latestVATInvNumber
    end
else
    begin

        select TOP 1 @latestNonVATInvNumber = invoiceNumber from dbo.Invoices where vatable = (select cast ('False' as bit)) ORDER BY invoiceID desc


        /* Invoice Number is numeric (non VAT) hence only incrementing it by 1 */

        set @latestNonVATInvNumber = cast(cast(@latestNonVATInvNumber as int) + 1 as nvarchar(10))                                                                                                                                                                                                                                                                                                       

        set @invoiceNumber = @latestNonVATInvNumber
    end

-- Return the result of the function
RETURN @invoiceNumber 

END

The part that is giving NULL is this piece of code:

select TOP 1 @latestNonVATInvNumber = invoiceNumber from dbo.Invoices where vatable = (select cast ('False' as bit)) ORDER BY invoiceID desc

and

 select TOP 1 @latestVATInvNumber = invoiceNumber from dbo.Invoices where vatable = 'true' ORDER BY invoiceID desc 

If I remove the 'DESC' then it gives me the result from database or even if 'ASC' is inserted but as is, the statement is returning NULL when there is a record for vatable = 'false' in the table.

Any suggestions?

Answer 1

Suggestion One (incorrect):

The first time you query the table, you use the following filter:

where vatable = 'true'

The second time you query it, you use the following:

(select cast ('False' as bit))

Change them to match each other by removing the CAST and using where vatable = 'false'.

Suggestion Two (Answer):

Add AND InvoiceNumber IS NOT NULL to your condition

Answer 2

With you using TOP 1 if you are returning more than 1 row then changing the order of the results returned could change the output given back to your @latestNonVATInvNumber variable.

What I would suggest is testing the top 1 part and try to select COUNT(*) to see how many rows are returned.

e.g.

select COUNT(*) from dbo.Invoices where vatable = (select cast ('False' as bit)) ORDER BY invoiceID desc

Then you could test what data is returned with something like

select * from dbo.Invoices where vatable = (select cast ('False' as bit)) ORDER BY invoiceID desc

Maybe then it will become clearer why a NULL is appearing.