Scalar function returns Null

[jimatqsi]

I cannot figure out why this function returns Null for every customer. Even when I run a query based on the credit card file, which means every row has something to be returned by this function.

The function should read all the rows in table x and concatenate the CC last 4 digits along with expiration info. So valid return values could look like this:
Visa 1234: 01 2014
Visa 2345: 02 2014 MCRD 1234 02 2014
....

I've played with the code. Sometimes it gets results and sometimes it doesn't and I haven't been smart enough to see what I did differently when it worked. I think it works sometimes when I return a value from the first query but it never works when I let go into the loop where it should concatenate info from multiple CCs.

Thanks for you time.

USE [MAS_SPC]
GO
/****** Object:  UserDefinedFunction [dbo].[DealerCreditCards]    Script Date: 04/16/2014 08:41:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Jim Wolf
-- Create date: 2/4/13
-- Description:    Returns concatenated list of brands for each vendor/productline combination
-- =============================================
ALTER FUNCTION  [dbo].[DealerCreditCards] 
(
    -- Add the parameters for the function here
    @DealerNo varchar(7))
RETURNS  NVARCHAR(100) 
AS
BEGIN
 
    -- Declare the return variable here
    DECLARE @ResultVar as nvarchar(100)
    declare @CC as nvarchar(50)
    declare @PriorCC as nvarchar(50)
    Declare @RowNumber as Integer
    declare @Row_Counter as nvarchar(3)
 
    Set @PriorCC = ''
    Set @CC = ''
    Select TOP 1 @CC=PaymentType + ' '+ Last4UnencryptedCreditCardNos + ': ' + ExpirationDateMonth + ' ' +ExpirationDateYear 
    FROM dbo.AR_CustomerCreditCard with (nolock)
    WHERE    (CustomerNo=@DealerNo )
    Order by PaymentType + ' '+ Last4UnencryptedCreditCardNos + ': ' + ExpirationDateMonth + ' ' +ExpirationDateYear 
 
        Select @Row_Counter=@@ROWCOUNT
--        SET @ResultVar =  @CC + '  '
 
        --    Select @ResultVar = @ResultVar + @CC + '  '
            Select @PriorCC = @CC
--SET @ResultVar =  @ResultVar  + @CC + '  '
--RETURN @ResultVar -- temporary to see if I can get anything back
WHILE @Row_Counter > 0
BEGIN
            SET @ResultVar =  @ResultVar  + @CC + '  '
            SET @PriorCC = @CC
    Select TOP 1 @CC=(PaymentType + ' '+ Last4UnencryptedCreditCardNos + ': ' + ExpirationDateMonth + ' ' +ExpirationDateYear) 
    FROM dbo.AR_CustomerCreditCard with (nolock)
    WHERE     CustomerNo=@DealerNo and (PaymentType + ' '+ Last4UnencryptedCreditCardNos + ': ' + ExpirationDateMonth + ' ' +ExpirationDateYear) >@PriorCC
    Order by PaymentType + ' '+ Last4UnencryptedCreditCardNos + ': ' + ExpirationDateMonth + ' ' +ExpirationDateYear 
    Select @Row_Counter=@@ROWCOUNT
CONTINUE
END
       RETURN @ResultVar 
 
END
 

Jim

[Rabbit]

If any of those fields are null, the result will be null. You should coalesce the nulls to a blank string before trying to append them.

'1234' + null = null

[jimatqsi]

Rabbit,
Bingo! Thank you so much. I had only to add initialization of the return variable and it runs like a champ.

I do so little SQL programming it feels like pulling teeth when I do it. But it feels so good to see it work. Thanks again.

Jim

[Rabbit]

No problem, good luck on the rest of your project.