USE [QYHYMeter]
GO
/****** Object: StoredProcedure [dbo].[GetTemp_YWMXJL] Script Date: 11/09/2010 11:37:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <lijinchang@shanghai3h,,Name>
-- Create date: <2010-05-20,,>
-- Description: <获取重复的明细记录第一条数据,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetTemp_YWMXJL]
@msg nvarchar(500) output
AS
SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
declare @S_TiaoXingM nvarchar(200)
declare @S_ShuiBiaoID nvarchar(200)
Set @msg = ''
--判断excel文件中条形码是否重复
select top(1) @S_TiaoXingM=S_TiaoXingM from Temp_BWCK_YeWuMXB group by S_TiaoXingM having count(S_TiaoXingM) > 1
if(@S_TiaoXingM != '')
BEGIN
Set @msg = '条形码' + @S_TiaoXingM + '重复！'
return
END
--判断excel文件中水表编号是否重复
select top(1) @S_ShuiBiaoID=S_ShuiBiaoID from Temp_BWCK_YeWuMXB group by S_ShuiBiaoID having count(S_ShuiBiaoID) > 1
if(@S_ShuiBiaoID != '')
BEGIN
Set @msg = '水表编号' + @S_ShuiBiaoID + '重复！'
return
END
--判断每条记录的格式，包括数据库中的条形码和水表编号是否已重复
declare @S_ShuiBiaoCJID nvarchar(500) --水表厂商
declare @S_BiaoXingID nvarchar(255) --水表型号
declare @S_KouJingID nvarchar(255) --口径
declare @S_XiangHao nvarchar(255) --箱号
declare @I_ShuiBiaoLC nvarchar(255) --量程
declare @D_ShengChanRQ nvarchar(255) --生产日期
declare @I_ShuiBiaoLX nvarchar(255) --表类型
select row_number() over(order by id) as 'index',* into #tmp from Temp_BWCK_YeWuMXB
declare @i int
select @i=1
while(@i<=(select count(1) from #tmp))
begin
--为存储过程参数赋值
select top(1) @S_ShuiBiaoCJID=S_ShuiBiaoCJID,
@D_ShengChanRQ=D_ShengChanRQ,
@S_KouJingID=S_KouJingID ,
@S_TiaoXingM=S_TiaoXingM,
@S_BiaoXingID=S_BiaoXingID,
@S_ShuiBiaoID=S_ShuiBiaoID,
@I_ShuiBiaoLC=I_ShuiBiaoLC,
@S_XiangHao=S_XiangHao,@I_ShuiBiaoLX=S_ShuiBiaoLX from #tmp where [index]=@i
exec [GetErrarContent] @S_ShuiBiaoCJID
,@S_BiaoXingID
,@S_KouJingID
,@S_TiaoXingM
,@S_ShuiBiaoID
,@S_XiangHao
,@I_ShuiBiaoLC
,@D_ShengChanRQ
,@I_ShuiBiaoLX
,@msg output
if(@msg = '')
begin
select @i=@i+1
end
else
begin
set @msg = 'excel第' + cast(@i as varchar(20)) + '行出错，错误信息为：' + @msg
drop table #tmp
return
end
end
drop table #tmp