Parse multiple synchronized comma delimited input in stored procedure-CodePudding

I have a stored procedure with 2 input parameters of type string (varchar(max)).

@draw = '172, 182, 167'
@game = '100, 101, 100'

I want to parse and store them in a table as below:

 |----------------------------------|
 |  draw           | game           |
 |----------------------------------|
 |  172            | 100            |
 |  182            | 101            |
 |  167            | 100            |
 |----------------------------------|

How can I achieve this? Any advice is appreciated, thanks.

This is what I have tried so far: I am able to split the string into two rows but the part I am unable to figure out is how do I parse 2 different inputs and put them into my table in a synchronized way as they are passed into the procedure .

this is my attempt to parse them:

create table report_game_draw (
    draw_id varchar(max) NULL,
    game_id varchar(max) NULL
)
go

Declare @draw nvarchar(MAX)
Declare @game nvarchar(MAX)

Set @draw ='188,189,190,191,192,193,194'
Set @game ='188,189,190,191,192,193,194'

DECLARE @XML AS XML
DECLARE @XML1 AS XML

DECLARE @Delimiter AS CHAR(1) =','

SET @XML = CAST(('<X>' REPLACE(@draw,@Delimiter ,'</X><X>') '</X>') AS XML)
SET @XML1 = CAST(('<X>' REPLACE(@game,@Delimiter ,'</X><X>') '</X>') AS XML)



INSERT INTO report_game_draw (draw_id,game_id)
((SELECT N.value('.', 'INT') AS ID FROM @XML.nodes('X') AS T(N),
(SELECT N.value('.', 'INT') AS ID FROM @XML1.nodes('X') AS T(N))

CodePudding user response：

There are lots of ways to approach this - note that although SQL Server offers a string_split function, it is only reliable for this particular use-case in Azure which supports a sequence number to guarantee ordering.

I tend to use Json functionality for this type of process, something like the following:

declare 
  @draw varchar(max) = '172, 182, 167', 
  @game varchar(max) = '100, 101, 100';

select Draw, Game 
from (
  select Convert(int, j.[value]) as Draw, 1   Convert(tinyint,j.[key]) Seq 
  from OpenJson(Concat('["',replace(@draw,',', '","'),'"]')) j
) d join (
  select Convert(int, j.[value]) as Game, 1   Convert(tinyint,j.[key]) Seq 
  from OpenJson(Concat('["',replace(@game,',', '","'),'"]')) j
) g on d.Seq = g.Seq

Example Fiddle