SOLVED: Stored procedure - importing an associated list as a parameter

Transact-SQL
1

I need to reconcile refunds in a database against an external input. This isn't the smartest way to do it, but it's the way that makes the most sense for this project.

I need to create a stored procedure that accepts a key & value as an input to create a temporary table. Though honestly I can't wrap my mind around it. I can either add variables as parameters & work on a one-by-one scale or I can input unordered lists as a parameter.

Is there a sensible way to import dictionaries as a parameter? If not are there better alternatives?

IF OBJECT_ID('tempdb..#RakRefunds') IS NOT NULL
   DROP TABLE #RakRefunds
CREATE TABLE #RakRefunds (OrderID NVARCHAR(400), RefundValue DECIMAL(18,2))

INSERT INTO #RakRefunds (OrderID,RefundValue) VALUES('77911333','89.99');
INSERT INTO #RakRefunds (OrderID,RefundValue) VALUES('77960358','12.67');
2

If you want to pass a "list" to an Sproc then you can put all the Key/Value pairs into a #TEMP table and that will be available to the SProc

or you could have a single delimited string variable and SPLIT that inside the SProc (into a #TEMP table).

It is also possible to pass an Array (as a table-valued parameter to the Sproc) direct from an APP but I've never bothered with that - probably just old-and-stuck-in-my-ways, I would be interested to know if others have adopted that functionality (it was added in SQL 2008 I think)

I suppose you could also pass the list as XML - but, for me, with some simple data (not including Line Breaks / embedded delimiters / character that need escaping, that would be overkill.

Do you really have Extended Characters in your Order IDs?

and Are they really that long?

3

Do you really have Extended Characters in your Order IDs?

and Are they really that long?

Nope. They're about 10 characters long on average.

I wound up going with DelimitedSplit8K and a substring. I'm not even sure where this falls on the efficiency spectrum, but there will never be more than 500 rows. So I'm calling it 'good enough'.

SELECT 
	SUBSTRING(item,0,CHARINDEX('|',item,0)) as OrderID
	,SUBSTRING(item,CHARINDEX('|',item,0)+1,10) as Fee
FROM [dbo].DelimitedSplit8K(@FeeOrderList,',')
4

I would too :slight_smile: From the tests I've done over the years Splitter-solutions work well, provided that the Splitter itself is efficient and Jeff's DelimitedSplit8K has a good reputation.