CODEWITHSUNDEEP
sql-servert-sqlselectstored-functions
user2043688
user2043688

Reputation: 15

Is it an SQL SERVER bug?

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?

Upvotes: 1

Views: 143

Answers (2)

AHiggins
AHiggins

Reputation: 7219

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

Upvotes: 2

kevchadders
kevchadders

Reputation: 8335

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.

Upvotes: 1

Related Questions