Passing multiple parameters within a single parameter to a Stored Procedure-CodePudding

The stored procedure only has 1 declared parameter as varchar(max).

I need to pass multiple parameters so I combine them in a string string such as below:

"Ref=2211010001165381;Src=ONLN;,Ref=2211010001165481;Src=ONLN;,Ref=2211010001165581;Src=ONLN;"

How can I split the values and assign them in their respective columns?

Below is my current query wherein Column Ref looks good:

WHILE LEN(@LISTMIXTRD) > 0 BEGIN
        select REPLACE(LEFT(@LISTMIXTRD, CHARINDEX(';Src=', @LISTMIXTRD ';Src=')-1),'Ref=','') as Ref   , LEFT(@LISTMIXTRD, CHARINDEX(';Src=', @LISTMIXTRD ';Src=')-1) as Src   SET @LISTMIXTRD = STUFF(@LISTMIXTRD, 1, CHARINDEX(',', @LISTMIXTRD ','), '')
END

CodePudding user response：

Honestly, I really suggest you change the input value to be something SQL Server can handle without a mess of string replacements. JSON or XML would be far better.

This solution attempts to convert the value to a JSON value, but it's based on your one example. If your data is in poorer condition, even more reason to fix the problem.

DECLARE @YourParameter nvarchar(MAX) = N'"Ref=2211010001165381;Src=ONLN;,Ref=2211010001165481;Src=ONLN;,Ref=2211010001165581;Src=ONLN;"';

SELECT OJ.Ref,
       OJ.Src
FROM STRING_SPLIT(@YourParameter,',') SS
     CROSS APPLY (VALUES(TRIM('"; ' FROM SS.[value])))V(Trimmed)
     CROSS APPLY (VALUES(CONCAT('{"',REPLACE(REPLACE(V.Trimmed,'=','":"'),';', '","'),'"}')))J(JSON)
     CROSS APPLY OPENJSON(J.JSON)
                 WITH(Ref varchar(20),
                      Src varchar(4)) OJ;

CodePudding user response：

Just another option is a conditional aggregation (it may be a little cleaner).

Example

Declare @S varchar(max) ='Ref=2211010001165381;Src=ONLN;,Ref=2211010001165481;Src=ONLN;,Ref=2211010001165581;Src=ONLN;'

Select Ref = max(case when B.value like 'Ref=%' then substring(B.value,5,250) end)
      ,Src = max(case when B.value like 'Src=%' then substring(B.value,5,250) end)
 From string_split(@S,',') A
 Cross Apply  string_split(A.value,';')  B
 Group By A.value

Results

Ref                 Src
2211010001165381    ONLN
2211010001165481    ONLN
2211010001165581    ONLN