What is the mistake in stored procedure?

Question

4.00/5 (1 vote)

See more:

Hi

i have a stored procedure as below

SQL

CREATE PROC TEST1
@valueList varchar(8000)

AS

BEGIN

DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

set @pos = 0
set @len = 0



WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
    set @value = SUBSTRING(@valueList, @pos, @len)
     
       print @value
        

    set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END



END

But when i input @valueList='A,B,C' it print only A AND B. why?
i want to print as below

A
B
C

THANKS

Posted 22-Jul-13 0:49am

hasbina

Updated 22-Jul-13 1:06am

v2

Add a Solution

3 solutions

Solution 2

It does not print the 'C' element of the list because the C is not followed by a comma, and after processing the B element, the while loop ends because it does not find another comma.

There are 2 simple solutions
1) As shown below : after the end of the while loop, print any remaining text
2) Add another 'dummy' comma separator at the end of the string before processing. This will ensure that the last element is processed by the code in the loop

DECLARE @pos INT
DECLARE @len INT

DECLARE @value nvarchar(4000)
 
set @pos = 0
set @len = 0
  
WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
	set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
	set @value = SUBSTRING(@valueList, @pos, @len)
	print @value

	set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END
print SUBSTRING(@valueList, @pos, @len)

Hope this helps
Ali.

Posted 22-Jul-13 1:10am

TrekkieTech

Solution 3

Because your loop is executing twice. If you input @valueList='A,B,C,' rather than @valueList='A,B,C' (a comma extra at end) then the result appears actually what you want. So change your input or change as it execute one extra (add a comma at end if no comma exist at end) or using loop counter + 1 (count comma first and add +1 with it in a variable).

Posted 22-Jul-13 1:12am

Amitava Bag (Kolkata)

Updated 22-Jul-13 1:15am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-07-22T01:05:00

Because there are only two commas in your string - so the WHILE test fails when you have processed the second one, and printed the second value.

The easiest way round this is to do what I did with a similar problem: Using comma separated value parameter strings in SQL IN clauses[^]

What it does is add an extra comma to the end of the string, then replace all double commas with a single comma - this has the advantage of getting rid of empty entries as well.