July 13, 2009 at 9:12 pm
I have a report with a multi-select list. Some of the items in the multi-select list have commas in them, like 'Virginia Beach, VA'. When I pass the list of selected items to a stored procedure as a comma delimited list ('Virginia Beach, VA, Norfolk, VA'), I'm unable to parse it out correctly within the stored procedure due to the commas within the comma delimited fields. Any ideas or suggestions on the best way to handle that? I'm querying a fairly large volume of data (a few million records) so performance will be a concern.
July 13, 2009 at 10:59 pm
Hi,
You delimited the comma separated from the string into the table and use the table in the stored procedure like
create table #temp
(
city varchar(30)
)
declare @abc varchar(1000)/*Alwayes should be in max value*/
select @abc = 'Virginia Beach,VA,Norfolk,VA'
select @abc = 'select ''' + replace (@ABC,',',''' union all select ''')+''''
insert into #temp (city)
exec (@ABC)
select *from #temp
ARUN SAS
July 14, 2009 at 8:42 am
My preference would be to remove the commas from the query that populates the list. This will be much easier than trying to parse out the commas.
This parses the commas and gets the data, but is ugly and may not be the best way to handle it:
DECLARE @test-2 VARCHAR(100), @value VARCHAR(100)
SET @test-2 = 'Virginia Beach, VA, Norfolk, VA'
SET @value = ''
;WITH cteTally AS
(
SELECT TOP 1000
ROW_NUMBER() OVER(ORDER BY AO.NAME) AS n
FROM
sys.all_objects AS AO CROSS JOIN
sys.all_objects AS AO2
)
SELECT
@value = @value + '|' + CASE WHEN @value = '' THEN SUBSTRING(',' + @test-2 + ',', n + 1,CHARINDEX(',', ',' + @test-2 + ',', CHARINDEX(',', ',' + @test-2 + ',', n + 1) + 1))ELSE
SUBSTRING(',' + @test-2 + ',', LEN(@value) + 1, LEN(',' + @test-2 + ',')) END
FROM
cteTally
WHERE
n < LEN(',' + @test-2 + ',') AND
SUBSTRING(',' + @test-2 + ',', n, 1) = ','
;WITH cteTally AS
(
SELECT TOP 1000
ROW_NUMBER() OVER(ORDER BY AO.NAME) AS n
FROM
sys.all_objects AS AO CROSS JOIN
sys.all_objects AS AO2
)
SELECT
LEFT(LTRIM(RTRIM(SUBSTRING(@value,N+1,CHARINDEX('|',@value,N+1)-N-1))), LEN(LTRIM(RTRIM(SUBSTRING(@value,N+1,CHARINDEX('|',@value,N+1)-N-1))))-1)
FROM
cteTally
WHERE
n 0
I'd replace the CTE's with a real tally table[/url] at the very least.
I still think removing the commas when creating the multi-select is the best way to do it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2009 at 7:42 am
Although your question asks how to parse out the various choices from the comma-delimited list, I can only assume that you don't have IDs associated with these various choices. By using an ID based parameter list, you no longer need to parse out the erroneous commas.
For example:
VALUE LABEL
1 Virginia Beach, VA
2 Norfolk, VA
3 Rochester, NY
4 Rochester, MN
So, regardless of how many choices a user selects, the values passed to the stored procedure will be numbers (e.g., "1,2" instead of "Virginia Beach, VA, Norfolk, VA").
--pete
July 15, 2009 at 8:40 am
You're correct. I am not able to use ID's to identify the items in the multi-select list.
July 16, 2009 at 3:47 am
You can change the delimeter of our string, use pipes("|") as the delimeter in place of commas.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply