检查数据是否已经存在的存储过程！

 

-- --------创建表脚本
CREATE   TABLE   [ VPMN_ClusterNoDet_ML ]  (
     [ ID ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ CND_BOpter ]   [ char ]  ( 2 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
     [ CND_BDptNumber ]   [ int ]   NULL  ,
     [ CND_EOpter ]   [ char ]  ( 2 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
     [ CND_EDptNumber ]   [ int ]   NULL  ,
     [ CND_AtValue ]   [ decimal ] ( 12 ,  2 )  NULL  ,
      PRIMARY   KEY    CLUSTERED  
    (
         [ ID ]
    )   ON   [ PRIMARY ]  
)  ON   [ PRIMARY ]
GO
-- --------------

-- Desc:检查数据是否已经存在的存储过程 createDate:2007-07-27
ALTER    PROCEDURE  VPMN_ClusterNoDetInsert_SML
(
     @CND_BOpter   char ( 2 ),      -- 起始操作符
     @CND_BDptNumber   int ,      -- 起始发展个数
     @CND_EOpter   char ( 2 ),      -- 结束操作符
     @CND_EDptNumber   int ,      -- 结束发展个数
     @CND_AtValue   decimal ( 12 , 2 ),  -- 考核系数值
     @msg   varchar ( 100 ) output  -- 返回的消息
)
AS
   set   @msg = ' ok '
   declare  mycursor  cursor  scroll dynamic
   for   select  CND_BDptNumber,CND_EDptNumber  from  VPMN_ClusterNoDet_ML
   open  mycursor
   declare   @BDptNumber  sysname
   declare   @EDptNumber  sysname
   fetch   next   from  mycursor  into   @BDptNumber , @EDptNumber
   while ( @@fetch_status = 0 )
     begin
       if   @CND_BDptNumber   >=   @BDptNumber   and   @CND_EDptNumber   <=   @EDptNumber
       begin
         set   @msg = ' 已经存在范围: ' +   @BDptNumber   + ' -- ' + @EDptNumber
       end
       fetch   next   from  mycursor  into   @BDptNumber , @EDptNumber
     end
   close  mycursor
   deallocate  mycursor
   if   @msg = ' ok '
     begin
     INSERT   INTO  VPMN_ClusterNoDet_ML(CND_BOpter,CND_BDptNumber,CND_EOpter,CND_EDptNumber,CND_AtValue)
         VALUES ( @CND_BOpter , @CND_BDptNumber , @CND_EOpter , @CND_EDptNumber , @CND_AtValue )
     end


GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO


-- ---------测试代码
select   *   from  VPMN_ClusterNoDet_ML

declare   @msg   varchar ( 100 )
exec  VPMN_ClusterNoDetInsert_SML  ' > ' , 20 , ' < ' , 100 , 1 , @msg  output
print   @msg