SQLTeam.com | Weblogs | Forums

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


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');


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?


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'.

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


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.