在存储过程中循环时出错 [英] Error while looping in store procedure

问题描述

亲爱的朋友



我在商店有问题程序，我有2个数据库在第一个数据库中我有3个表。从这三个我想合并并移动到另一个数据库，我在第二个数据库中创建临时表从他们将数据移动到表中，但问题是当我循环临时表

在第二行数据将与第一行数据合并，



第一行数据

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

第二排数据

/ BOM / STV / STV / BOM / BOM / BLR / PAT / DEL / DEL / HYD [在线数据来自第1排]

/ 523/524/524/ 191/387 [Under行数据来自第1行]

/ SG / SG / SG / 6E / 6E [线下数据来自第1行]

/ X9M8VG / X9M8VG / X9M8VG / I4R7QC / I4R7QC [线下数据来自第1排]



我试过这种方法它是不工作

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

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

任何人都可以帮我解决此问题



问候

Sheethal

Any one can help me to resolve this issue

Regards
Sheethal

推荐答案

我不确定你要做的是什么，但是：

1）你的代码不完整，

2）你没有提供错误信息或问题详情

3）听起来你想要的计算每个字符串中的部分。



广告。 1）和2）

查看你的代码并阅读我的评论：

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:

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

你看到你的错误吗？



广告3）

如果您想计算零件，请尝试以下方法：

Do you see your mistakes?

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

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

这篇关于在存储过程中循环时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！