3 Arrays as the input parameters
-
Hello,
the user will create 3 parameters for the procedure,
something like this
exec p_test_account '001|002|003','6405|1059|6615','01485600|01476500|01452540'
can be less inputs (1) or can be more (10), can be missing leading zeros, can be shorter then need, but the same way - pipe delimited and 3 strings.... crazy...
these values should be put onto the table as column 1for the first array, column 2 - for the second and column 3 fro the last one.
I can not use substring and count as the length could be different, the only "border" between the values - pipe |...
Right now - adding into the @X all characters up to the pipe, then breaking...then inserting...
Maybe there is a different way to make it better and faster (and less coding?:rolleyes
THANKS!!!
-
I would suggest using xml input string instead of what you have listed, It seems your trying to do too much, and also trying to do something that sql does not do well.
to continue on what you have, you'll have to create code to parse apart the string.
Give this a read
-
I agree with Ray, but for what it's worth...
--preparation (adapted from http://www.mindsdoor.net/SQLTsql/ParseCSVString.html)
create function fn_ParseCSVString (@CSVString varchar(8000), @Delimiter varchar(10))
returns @tbl table (row int identity(1, 1), s varchar(1000))
as
begin
declare @i int, @j-2 int
set @i = 1
while @i <= len(@CSVString)
begin
set @j-2 = charindex(@Delimiter, @CSVString, @i)
if @j-2 = 0
set @j-2 = len(@CSVString) + 1
insert @tbl select substring(@CSVString, @i, @j-2 - @i)
select @i = @j-2 + len(@Delimiter)
end
return
end
go
--inputs
declare @s1 varchar(50), @s2 varchar(50), @s3 varchar(50)
select @s1 = '001|002|003', @s2 = '6405|1059|6615', @s3 = '01485600|01476500|01452540'
--calculation
select
max(case when col = 1 then s end) as c1,
max(case when col = 2 then s end) as c2,
max(case when col = 3 then s end) as c3
from (
select 1 as col, * from dbo.fn_ParseCSVString (@s1, '|')
union all select 2 , * from dbo.fn_ParseCSVString (@s2, '|')
union all select 3 , * from dbo.fn_ParseCSVString (@s3, '|')) a
group by row
--tidy
go
drop function fn_ParseCSVString
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Ryan, thanks! I will try this way, I have already something, I will drop it here also 
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply