Error while looping in store procedure

Question

4.00/5 (1 vote)

See more:

Dear Friends

I Have problem in store Procedure, I have 2 Database In 1st database I have 3 Tables. From these three I want to merge and moved to another database, I created temporary table in 2nd database from their i moving the data into table, But problem is when I loop the temporary table
in second row of data will merge with first row of data,

1st Row of Data

BLR/BOM/BOM/STV/STV/BOM/BOM/BLR
523/523/524/524
SG/SG/SG/SG
X9M8VG/X9M8VG/X9M8VG/X9M8VG

2nd Row of Data
/BOM/STV/STV/BOM/BOM/BLR/PAT/DEL/DEL/HYD [Under line data it is from 1st Row]
/523/524/524/191/387 [Under line data it is from 1st Row]
/SG/SG/SG /6E/6E [Under line data it is from 1st Row]
/X9M8VG/X9M8VG/X9M8VG /I4R7QC/I4R7QC [Under line data it is from 1st Row]

I tried this method it is not working

SQL

Declare @seglth int;
set @seglth =COUNT(@segment);
   IF(0<=@seglth)
    BEGIN
       SET @segment='';
       SET @segment+='';
    END

Any one can help me to resolve this issue

Regards
Sheethal

Posted 16-Apr-14 19:30pm

smsheethal

Updated 20-Apr-14 9:43am

Manas Bhardwaj

v2

Add a Solution

Comments

Nayan Ambaliya 17-Apr-14 2:27am

Please clarify your question details more accurately.. Sounds simple but if you can clarify little bit more ('[Under line data it is from 1st Row]' does not help..!!)

King Fisher 21-Apr-14 5:36am

not clear.

RDBurmon 1-May-14 11:12am

Provide answer to below questions to understand your query and give you solution
1) How many columns are there in source tables?
2) Do you want to UNION all three tables and dump it to destination table?
4) Or do you want to concatenate each row value to respective row values from other two tables in source database and then dump into destination table?

1 solution

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)

Maciej Los · Answer 1 · 2014-05-24T07:33:00

I'm not sure what you're trying to achieve, but:
1) your code is incomplete,
2) you not provided error message or details of issue
3) it sounds like you want to count the parts in each string.

Ad. 1) and 2)
Have a look at your code and read my comments:

SQL

Declare @seglth int;
set @seglth =COUNT(@segment);
   IF(0<=@seglth)
    BEGIN
       --set @segment variable to ''
       SET @segment='';
       --@segment variable is equal to ''
       --add '' to ''
       SET @segment+='';
    END

Do you see your mistakes?

Ad 3)
If you would like to count parts, try this:

SQL

DECLARE @src TABLE (SomeText VARCHAR(155))
INSERT INTO @src (SomeText)
VALUES('BLR/BOM/BOM/STV/STV/BOM/BOM/BLR'),
('523/523/524/524'),
('SG/SG/SG/SG'),
('X9M8VG/X9M8VG/X9M8VG/X9M8VG')

DECLARE @tmp TABLE(RowNo INT, StepNo INT, OrygText VARCHAR(155), MyPart VARCHAR(10))

;WITH MyText AS
(	
	--initial value
	SELECT SomeText As OrygText, LEFT(SomeText, CHARINDEX('/', SomeText)-1) AS MyPart, RIGHT(SomeText, LEN(SomeText) - CHARINDEX('/', SomeText)) AS Remainder, 
			ROW_NUMBER() OVER(ORDER BY SomeText) AS RowNo, 1 AS StepNo
	FROM @src
	WHERE CHARINDEX('/', SomeText)>0
	UNION ALL
	--recursive part
	SELECT OrygText, LEFT(Remainder, CHARINDEX('/', Remainder)-1) AS MyPart, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('/', Remainder)) AS Remainder, RowNo, StepNo + 1 AS StepNo
	FROM MyText
	WHERE CHARINDEX('/', Remainder)>0
	UNION ALL
	SELECT OrygText, Remainder AS MyPart, NULL AS Remainder, RowNo, StepNo + 1 AS StepNo
	FROM MyText
	WHERE CHARINDEX('/', Remainder)=0
)
INSERT INTO @tmp (RowNo, StepNo, OrygText, MyPart)
SELECT RowNo, StepNo, OrygText, MyPart
FROM MyText
ORDER BY RowNo, StepNo

--SELECT *
--FROM @tmp 
--ORDER BY RowNo, StepNo

SELECT RowNo, OrygText, MyPart, COUNT(MyPart) AS Parts
FROM @tmp
GROUP BY RowNo, OrygText, MyPart

Result:

RNo OrygText                        MyPart Parts
1   523/523/524/524                 523    2
1   523/523/524/524                 524    2
2   BLR/BOM/BOM/STV/STV/BOM/BOM/BLR BLR    2
2   BLR/BOM/BOM/STV/STV/BOM/BOM/BLR BOM    4
2   BLR/BOM/BOM/STV/STV/BOM/BOM/BLR STV    2
3   SG/SG/SG/SG                     SG     4
4   X9M8VG/X9M8VG/X9M8VG/X9M8VG     X9M8VG 4