Reputation: 226
I'm trying to create a stored procedure for updating a table in a batch. I want to take parameters in as a nvarchar
and call string_split
on them.
@ParamList1 NVARCHAR(max) = '1,2,3,4,5'
@ParamList2 NVARCHAR(max) = 'a,b,c,d,e'
I want to get a temporary table like
Param1 Param2
1 a
2 b
3 c
...
How would I do this?
Upvotes: 0
Views: 182
Reputation: 67311
You've got an answer already, which is working fine, but this should be faster and easier:
You did not specify your SQL-Server's version, but - talking about STRING_SPLIT()
- I assume it's at least v2016. If this is correct, you can use OPENJSON. Your list of numbers needs nothing more than brackets to be a JSON-array ([1,2,3]
), while an array of words/letters can be transformed with some easy string operations (["a","b","c"]
).
Following the docs, OPENJSON returns the elements position in [key]
, while the element itself is returned in [value]
. You can simply JOIN these sets:
DECLARE @ParamList1 NVARCHAR(max) = '1,2,3,4,5';
DECLARE @ParamList2 NVARCHAR(max) = 'a,b,c,d,e';
SELECT p1.[key] AS FragmentNr
,p1.[value] AS P1
,p2.[value] AS P2
FROM OPENJSON(CONCAT('[',@ParamList1 + ']')) p1
INNER JOIN OPENJSON(CONCAT('["',REPLACE(@ParamList2,',','","'),'"]')) p2 ON p1.[key]=p2.[key] ;
In this answer you will find some details (UPDATE section 1 and 2).
Upvotes: 0
Reputation: 1270331
Unfortunately, string_split()
does not guarantee ordering or provide a position argument (Microsoft are you listening?).
So, the safest method is a recursive CTE (or perhaps another approach using XML):
with cte as (
select convert(nvarchar(max), NULL) as x1, convert(nvarchar(max), NULL) as x2, @paramlist1 as rest1, @paramlist2 as rest2, 1 as lev
union all
select convert(nvarchar(max), left(rest1, charindex(',', rest1 + ',') - 1)),
convert(nvarchar(max), left(rest2, charindex(',', rest2 + ',') - 1)),
stuff(rest1, 1, charindex(',', rest1 + ','), ''),
stuff(rest2, 1, charindex(',', rest2 + ','), ''),
lev + 1
from cte
where rest1 <> '' and rest2 <> ''
)
select *
from cte
where x1 is not null;
Here is a db<>fiddle.
Upvotes: 2