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.
Jim
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.
Code:
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
Comment