asked on
2 comma seperated list - SQL Server
Hello
I have a stored procedure in sql server 2012 which I pass in a comma separated value, loop it and update a table...all good.
but now I have 2 lists to handle now which they need to be paired in order, the lists contains....E.G
list 1 -SER123, SER456, SER789
list 2 - MOD123, MOD456, MOD789
The table for example will have 3 columns - Order, Serial, Module
the position of these 2 lists are paired (SER123- MOD123) so when I update the table they need to be in the same row.
See the attachment of how I do just now with one list.
Any help greatly appreciated.
comma-sep-SP.docx
Thanks
I have a stored procedure in sql server 2012 which I pass in a comma separated value, loop it and update a table...all good.
but now I have 2 lists to handle now which they need to be paired in order, the lists contains....E.G
list 1 -SER123, SER456, SER789
list 2 - MOD123, MOD456, MOD789
The table for example will have 3 columns - Order, Serial, Module
the position of these 2 lists are paired (SER123- MOD123) so when I update the table they need to be in the same row.
See the attachment of how I do just now with one list.
Any help greatly appreciated.
comma-sep-SP.docx
Thanks
Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
razza_b
Is there a particular reason why you need to pass comma-separated strings? Can you explore using table-valued parameters to your procedure?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for reply guys.
Arif Ansari - The first case is nearly what I need except I cant have the 2 values concat in same column with hyphen.
The table will consist of Order, Serial, Module, Date and they need to be put into their specified column.
:)
Arif Ansari - The first case is nearly what I need except I cant have the 2 values concat in same column with hyphen.
The table will consist of Order, Serial, Module, Date and they need to be put into their specified column.
:)
Please refer the logic i have posted.
It has the logic to combine two column with hyphen.
Thanks
It has the logic to combine two column with hyphen.
Thanks
ASKER
sorry Arif Ansari....not with the values concatenated into same column, need to be split apart into the respective column...
Thanks :)
Thanks :)
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yes exactly like that...
SalesOrder List1 List2 Date
1 SER123 MOD123 2016-09-23 13:03:08.440
sorry where and what do I replace to use this?
SalesOrder List1 List2 Date
1 SER123 MOD123 2016-09-23 13:03:08.440
sorry where and what do I replace to use this?
ASKER
Its ok I got it...very nice...
ASKER
SalesOrder Serial Module Date
test1234 SER123 MOD123 2016-09-23 01:18:31.040
test1234 SER456 MOD456 2016-09-23 01:18:31.057
test1234 SER789 MOD789 2016-09-23 01:18:31.067
test1234 SER123 MOD123 2016-09-23 01:18:31.040
test1234 SER456 MOD456 2016-09-23 01:18:31.057
test1234 SER789 MOD789 2016-09-23 01:18:31.067
ASKER
thank you!!
ALTER PROCEDURE [dbo].[MyProc]
@SalesOrder nvarchar(20),
@SerialList nvarchar(max),
@ModuleList nvarchar(max)
AS
DECLARE @xml AS XML;
select @xml = '<p>' + replace(@SerialList, ',' ,'</p><p>') + '</p>';
SELECT IDENTITY(INT, 1, 1) AS [ID],
P.value('.','varchar(16)') AS [Param]
INTO #Serials
FROM @xml.nodes('p') P(p);
SELECT @xml = '<p>' + replace(@ModuleList, ',' ,'</p><p>') + '</p>';
SELECT IDENTITY(INT, 1, 1) AS [ID],
P.value('.','varchar(16)') AS [Param]
INTO #Modules
FROM @xml.nodes('p') P(p);
INSERT INTO OrderLog (SalesOrder, Serial, Module, Date)
SELECT @SalesOrder, s.Param, m.Param, GETDATE()
FROM #Serials s
INNER JOIN #Modules m ON s.ID = m.ID;
ASKER
WOW now that's a very nice way to do it....I like it!!