2

I am building an application using Microsoft Access as a front-end and SQL Server as the back end.

I have a Stored Procedure that inserts customer information. One of the pieces of information that will be inserted for each customer are their Credit Card numbers. The number of credit cards for each customer could be anywhere from 1-50.

I am planning on using a Dynamic array in VBA to add the credit card numbers. The trouble I am having is I am not quite sure how to pass the values to the stored procedure.

My first thought was to create a ton of parameters (CC Number 1, CC Number 2, CC Number 3,etc) but obviously this isn't the correct way to do it.

After the credit card numbers have been added to the Dynamic Array, I can ReDim it to get the count of how many credit card numbers I will need to insert.

What would be the best way to pass lets say, 14 credit card values to one parameter and insert each value as a new row?

Adib Akale
  • 105
  • 9
  • 2
    Table valued parameters? https://msdn.microsoft.com/en-us/library/bb510489.aspx – Kritner Apr 19 '16 at 15:20
  • @Kritner: But how would one call this from Access? -- If a stored procedure is a must, I would probably join them together and pass as one delimited string. Then use a [Split function](http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) of your choice to extract them. But actually I'd rather insert the rows directly from Access. – Andre Apr 19 '16 at 16:17
  • @Andre How would I insert each split value as a new record in SQL Server? I am thinking of maybe doing something like this in Access `For Each....value in array` `execute stored procedure` `next` Although I feel there is a much better way to do this. – Adib Akale Apr 19 '16 at 16:51
  • I meant, I wouldn't have this as part of a stored procedure, thus no joining or splitting. Somewhere you collect these CC numbers, and from there I would directly insert them into the table (execute INSERT statement(s) or with DAO). – Andre Apr 19 '16 at 18:13
  • @AdibAkale if your question has been answered, please accept the answer. If not, please let use know what you are still missing :) – C. White May 17 '16 at 18:02

2 Answers2

1

Unfortunately, AFAIK, VBA doesn't support table valued parameters (ADO.NET does, but not the vanilla VB6 implementation that we have to use in Access).

You could send a stream of function calls to the server by looping through a recordset or something, but that's going to be painfully slow. If you don't mind repeating yourself, though, you could send one big command that contains a bunch of EXEC statements chained one after the other (if you're using DAO, be sure to specify SET NOCOUNT ON at the start of the query).

If you're looking for a pure-SQL solution, here's the strategy I take for these sorts of problems:

  • Concatenate the array of values into a string with some sort of separator (e.g. "," or "|").
  • Pass the string to a function or query that converts it to a table of values.
  • INSERT or MERGE the table of values into the final target table.

Here's an example of how you can do it:

SET NOCOUNT ON

DECLARE @x XML;
DECLARE @CreditCards AS TABLE (CreditCardNumber VARCHAR(16));
DECLARE @FinalTable AS TABLE (CreditCardNumber VARCHAR(16));
DECLARE @CreditCardList AS VARCHAR(8000);
DECLARE @Divider AS Varchar(10);

SET @Divider=',';
SET @CreditCardList='1234567890123456,1111111111111111,2222222222222222,123456789012345';

IF NOT @CreditCardList IS NULL 
  BEGIN 
    SELECT @x = CAST('<A>'+ REPLACE(@CreditCardList,@Divider,'</A><A>')+ '</A>' AS XML);
    INSERT INTO 
      @CreditCards            
    SELECT 
      t.value('.', 'varchar(16)') AS inVal
    FROM 
      @x.nodes('/A') AS x(t) ;
  END 

INSERT INTO 
  @FinalTable
SELECT 
  CreditCardNumber
FROM
  @CreditCards 

SELECT * FROM @FinaLTable

XML isn't the fastest way to do the conversion, but it has the benefit of being relatively straightforward. Jeff Moden provides several pretty inspired approaches to the problem in his blog post Tally OH! An Improved SQL 8K “CSV Splitter” Function.

Hope that helps!

C. White
  • 802
  • 1
  • 7
  • 19
0

Use XML to pass all customer information in one document. This way you can pass any number of parameters and query it on server as a table.

Y.B.
  • 3,526
  • 14
  • 24