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!!!
-
Yep,
you're right. This is crazy
. Anyway, here goes:--assumes that all params have same number of inputs
while charindex('|',@param1) > 0
begin
set @insert_param1 = substring(@param1,1,charindex('|',@param1)-1)
set @insert_param2 = substring(@param2,1,charindex('|',@param2)-1)
set @insert_param3 = substring(@param3,1,charindex('|',@param3)-1)
insert into mytable (col1, col2, col3)
values (@insert_param1, @insert_param2, @insert_param3)
set @param1 = substring(charindex('|',@param1) + 1)
set @param2 = substring(charindex('|',@param2) + 1)
set @param3 = substring(charindex('|',@param3) + 1)
end
Hope that helps,
-
This looks to me like an ideal candidate for using XML to pass your parameters.
I'm currently on vacation and away from my archives, but if this is of interest early next week, please let me know and I'll post some samples.
Take care..- Ward Pond
blogs.technet.com/wardpond -
SQLZ - it helped A LOT!!!! THANKS!!!
Ward - yes, but the "client" wants it that way.... what we can do? 🙂
-
Is the client SQL savvy?
Are they aware of the XML enahncements in SQL Server?
You've perhaps got an opportunity to educate them.. but if they're resistant, I suppose you should give them what they're asking for.
In general, though, I try to get my clients to tell me what they want, and I ask them to leave the how to me.
Sometimes it works, sometimes it doesn't..
Take care..- Ward Pond
blogs.technet.com/wardpond -
I agree with what you're saying. XML is a great way to pass arrays of variables into procedures, especially if the arrays need to be multidimensional in nature... parsing apart strings just becomes difficult at that point. At least you can declare string variables as varchar(max) now, but in these sorts of situations, XML is probably the best way to go.
If the client disagrees, show them the light.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply