我写了一个存储过程, 无任何参数,
运行也不会有异常, 但也得不到想要的结果, 请问有没有好的方法能尽快找出哪总份代码没有按我希望的方式运行?---------------------
CREATE PROCEDURE proc_Emb_Data_Collection
ASDECLARE @jobNumber NVARCHAR(6) -- 用于存储制单号
DECLARE @style_Number NVARCHAR(5) -- 用于存制单的款号(即制单的后5位数字)
DECLARE @task_ID smallint -- 用于存储工序号
DECLARE @task_Name nvarchar(50) -- 用于存储工序名
DECLARE @color_Number nvarchar(20) -- 用于存储色号
DECLARE @build_Date smalldatetime -- 用于存储扫菲日期
DECLARE @in_Quantity int -- 用于存储入菲数
DECLARE @out_Quantity int -- 用于存储出菲数
DECLARE @job_Sheet_ID int -- 用于存储制单ID
DECLARE @cut_Amount int -- 用于存储裁数DECLARE @strSql NVARCHAR(500) -- 用于存储创建游标的SQL语句,用于动态创建游标--取得最近两个月的制单列表
DECLARE cur_JobNumber INSENSITIVE CURSOR FOR
SELECT 'T' + RIGHT(jsnumber,5) AS JobNumber FROM Job_Sheet
WHERE Id IN (SELECT Job_Sheet_Id FROM Job_Sheet_CutAmount
WHERE Build_Date BETWEEN GETDATE() - 60 AND GETDATE())OPEN cur_JobNumber
FETCH NEXT FROM cur_JobNumber INTO @jobNumber
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @style_Number = SUBSTRING(@jobNumber, 2, 5) -- 取得该份制单的款号(即制单的后5位数字)
-- 检查指定用于存储绣花片收/发记录的数据表是否存在, 如果不存在,则进入下一个循环
IF NOT EXISTS(SELECT * FROM server2.hoyida.dbo.SysObjects WHERE XType = 'U' AND Name = @jobNumber)
BEGIN
BREAK
END
-- 取得发绣花片的工序号(因为同一款可能存在多个绣花片,故此处要用游标
DECLARE cur_In_GongXuHao INSENSITIVE CURSOR FOR
SELECT GongXuHao FROM server2.hoyida.dbo.style WHERE GongXuMing LIKE '%收%绣%' AND KuanHao = @style_Number
OPEN cur_In_GongXuHao
FETCH NEXT FROM cur_In_GongXuHao INTO @task_ID
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @task_Name = GongXuMing FROM Style WHERE KuanHao = @style_Number AND GongXuHao = @task_ID -- 取得工序名
-- 取得收该款的绣花片记录
SET @strSql = N'DECLARE cur_In_Record INSENSITIVE CURSOR FOR '
SET @strSql = @strSql + N' SELECT SELECT CONVERT(VARCHAR(10),riqi,120) AS Build_Date, SeHao AS Color_Number, SUM(ShuLiang) AS IN_Quantity, Out_Quantity = 0 '
SET @strSql = @strSql + N' FROM ' + @jobNumber + ' WHERE GongHao = ''826355'' AND GongXu = ' + @task_ID + ' GROUP BY RiQi, GongXu, SeHao '
EXECUTE sp_exeutesql @strSql
OPEN cur_In_Record
FETCH NEXT FROM cur_In_Record INTO @build_Date, @color_Number, @in_Quantity, @out_Quantity
WHILE(@@FETCH_STATUS = 0)
BEGIN
-- 取得裁数
SET @strSql = N'SELECT @cut_Amount = SUM(shuliang) FROM C' + @style_Number + ' WHERE SeHao = ''' + @color_Number + ''''
EXECUTE sp_executesql @strSql -- 取得Job_Sheet_ID(制单ID)
SELECT @job_Sheet_ID = [ID] FROM Job_Sheet WHERE RIGHT(JSNumber, 5) = @style_Number -- 判断该收绣花片记录是不是否已收集,如果收集,则更新,否则新插入该记录
IF EXISTS (SELECT * FROM EMB_Detail WHERE Style_Number = @style_Number AND Color_Number = @color_Number AND Build_Date = @build_Date AND Task_ID = @task_ID)
BEGIN
UPDATE Emb_Detail SET Task_Name = @task_Name, In_Quantity = @in_Quantity, Out_Quantity = @out_Quantity, Job_Sheet_ID = @Job_Sheet_ID, Cut_Amount = @cut_Amount
WHERE Style_Number = @style_Number AND Color_Number = @color_Number AND Build_Date = @build_Date AND Task_ID = @task_ID
END
ELSE
BEGIN
INSERT INTO Emb_Detail (Style_Number, Color_Number, Build_Date, Task_ID, Task_Name, In_Quantity, Out_Quantity, Job_Sheet_ID, Cut_Amount)
VALUES (@style_Number, @color_Number, @build_Date, @task_ID, @task_Name, @in_Quantity, @out_Quantity, @job_Sheet_ID, @cut_Amount )
END
FETCH NEXT FROM cur_In_Record INTO @build_Date, @color_Number, @in_Quantity, @out_Quantity
END
CLOSE cur_In_Record
DEALLOCATE cur_In_Record --
FETCH NEXT FROM cur_In_GongXuHao INTO @task_ID
END
CLOSE cur_In_GongXuHao
DEALLOCATE cur_In_GongXuHao FETCH NEXT FROM cur_JobNumber INTO @jobNumber
END
CLOSE cur_JobNumber
DEALLOCATE cur_JobNumber
运行也不会有异常, 但也得不到想要的结果, 请问有没有好的方法能尽快找出哪总份代码没有按我希望的方式运行?---------------------
CREATE PROCEDURE proc_Emb_Data_Collection
ASDECLARE @jobNumber NVARCHAR(6) -- 用于存储制单号
DECLARE @style_Number NVARCHAR(5) -- 用于存制单的款号(即制单的后5位数字)
DECLARE @task_ID smallint -- 用于存储工序号
DECLARE @task_Name nvarchar(50) -- 用于存储工序名
DECLARE @color_Number nvarchar(20) -- 用于存储色号
DECLARE @build_Date smalldatetime -- 用于存储扫菲日期
DECLARE @in_Quantity int -- 用于存储入菲数
DECLARE @out_Quantity int -- 用于存储出菲数
DECLARE @job_Sheet_ID int -- 用于存储制单ID
DECLARE @cut_Amount int -- 用于存储裁数DECLARE @strSql NVARCHAR(500) -- 用于存储创建游标的SQL语句,用于动态创建游标--取得最近两个月的制单列表
DECLARE cur_JobNumber INSENSITIVE CURSOR FOR
SELECT 'T' + RIGHT(jsnumber,5) AS JobNumber FROM Job_Sheet
WHERE Id IN (SELECT Job_Sheet_Id FROM Job_Sheet_CutAmount
WHERE Build_Date BETWEEN GETDATE() - 60 AND GETDATE())OPEN cur_JobNumber
FETCH NEXT FROM cur_JobNumber INTO @jobNumber
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @style_Number = SUBSTRING(@jobNumber, 2, 5) -- 取得该份制单的款号(即制单的后5位数字)
-- 检查指定用于存储绣花片收/发记录的数据表是否存在, 如果不存在,则进入下一个循环
IF NOT EXISTS(SELECT * FROM server2.hoyida.dbo.SysObjects WHERE XType = 'U' AND Name = @jobNumber)
BEGIN
BREAK
END
-- 取得发绣花片的工序号(因为同一款可能存在多个绣花片,故此处要用游标
DECLARE cur_In_GongXuHao INSENSITIVE CURSOR FOR
SELECT GongXuHao FROM server2.hoyida.dbo.style WHERE GongXuMing LIKE '%收%绣%' AND KuanHao = @style_Number
OPEN cur_In_GongXuHao
FETCH NEXT FROM cur_In_GongXuHao INTO @task_ID
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @task_Name = GongXuMing FROM Style WHERE KuanHao = @style_Number AND GongXuHao = @task_ID -- 取得工序名
-- 取得收该款的绣花片记录
SET @strSql = N'DECLARE cur_In_Record INSENSITIVE CURSOR FOR '
SET @strSql = @strSql + N' SELECT SELECT CONVERT(VARCHAR(10),riqi,120) AS Build_Date, SeHao AS Color_Number, SUM(ShuLiang) AS IN_Quantity, Out_Quantity = 0 '
SET @strSql = @strSql + N' FROM ' + @jobNumber + ' WHERE GongHao = ''826355'' AND GongXu = ' + @task_ID + ' GROUP BY RiQi, GongXu, SeHao '
EXECUTE sp_exeutesql @strSql
OPEN cur_In_Record
FETCH NEXT FROM cur_In_Record INTO @build_Date, @color_Number, @in_Quantity, @out_Quantity
WHILE(@@FETCH_STATUS = 0)
BEGIN
-- 取得裁数
SET @strSql = N'SELECT @cut_Amount = SUM(shuliang) FROM C' + @style_Number + ' WHERE SeHao = ''' + @color_Number + ''''
EXECUTE sp_executesql @strSql -- 取得Job_Sheet_ID(制单ID)
SELECT @job_Sheet_ID = [ID] FROM Job_Sheet WHERE RIGHT(JSNumber, 5) = @style_Number -- 判断该收绣花片记录是不是否已收集,如果收集,则更新,否则新插入该记录
IF EXISTS (SELECT * FROM EMB_Detail WHERE Style_Number = @style_Number AND Color_Number = @color_Number AND Build_Date = @build_Date AND Task_ID = @task_ID)
BEGIN
UPDATE Emb_Detail SET Task_Name = @task_Name, In_Quantity = @in_Quantity, Out_Quantity = @out_Quantity, Job_Sheet_ID = @Job_Sheet_ID, Cut_Amount = @cut_Amount
WHERE Style_Number = @style_Number AND Color_Number = @color_Number AND Build_Date = @build_Date AND Task_ID = @task_ID
END
ELSE
BEGIN
INSERT INTO Emb_Detail (Style_Number, Color_Number, Build_Date, Task_ID, Task_Name, In_Quantity, Out_Quantity, Job_Sheet_ID, Cut_Amount)
VALUES (@style_Number, @color_Number, @build_Date, @task_ID, @task_Name, @in_Quantity, @out_Quantity, @job_Sheet_ID, @cut_Amount )
END
FETCH NEXT FROM cur_In_Record INTO @build_Date, @color_Number, @in_Quantity, @out_Quantity
END
CLOSE cur_In_Record
DEALLOCATE cur_In_Record --
FETCH NEXT FROM cur_In_GongXuHao INTO @task_ID
END
CLOSE cur_In_GongXuHao
DEALLOCATE cur_In_GongXuHao FETCH NEXT FROM cur_JobNumber INTO @jobNumber
END
CLOSE cur_JobNumber
DEALLOCATE cur_JobNumber
和
select 要好好利用撒!