SQL Server - parameter with unknown number of multiple values

Question

I'm creating a grid that has two columns: Name and HotelId. The problem is that data for this grid should be sent with a single parameter of VARCHAR type and should look like this:

@Parameter = 'Name1:5;Name2:10;Name3:6'

As you can see, the parameter contains Name and a number that represents ID value and you can have multiple such entries, separated by ";" symbol.

My first idea was to write a query that creates a temp table that will have two columns and populate it with data from the parameter.

How could I achieve this? It seems like I need to split the parameter two times: by the ";" symbol for each row and then by ":" symbol for each column. How should I approach this?

Also, if there is any other more appropriate solution, I'm open to suggestions.

Answer 1

First Drop the #temp table if Exists...

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
/*Then it exists*/
DROP TABLE #temp

Then create #temp table

CREATE TABLE #temp (v1 VARCHAR(100))

Declare all the @Paramter....

DECLARE @Parameter VARCHAR(50)
SET @Parameter= 'Name1:5;Name2:10;Name3:6'
DECLARE @delimiter nvarchar(1) 
SET @delimiter= N';';

Here, Inserting all @parameter value into #temp table using ';' separated..

INSERT INTO #temp(v1)
SELECT * FROM(

SELECT v1 = LTRIM(RTRIM(vals.node.value('(./text())[1]', 'nvarchar(4000)')))
FROM (
    SELECT x = CAST('<root><data>' + REPLACE(@Parameter, @delimiter, '</data><data>') + '</data></root>' AS XML).query('.')
) v
CROSS APPLY x.nodes('/root/data') vals(node)
)abc

After inserting the value into #temp table..get all the value into ':' seprated...

select  Left(v1, CHARINDEX(':', v1)-1) as Name , STUFF(v1, 1, CHARINDEX(':', v1), '') as HotelId FROM #temp

Then you will get this type of Output