SQL 存储过程有效但有错误 [英] SQL stored procedure works but with errors

问题描述

我的表有 3 个主键:CODE_TARIF、UNITE 和 MODE_LIV.

我编写了一个用于复制和粘贴的存储过程，但具有不同的 MODE_LIV.

例如:如果我在表 T_TARIF 中已经有 2 行 MODE_LIV = 2， 当我使用输入 MODE_LIV =3 运行此存储过程时>，我将有 4 行.

ALTER PROCEDURE [dbo].[Copy_Tarif]-- 这里添加存储过程的参数@MODE_LIV int作为开始声明@CODE_TARIF varchar(15)声明@ZONE int声明@UNITE int声明@LIBELLE varchar(30)声明 @TR_DEB int声明 @TR_FIN int声明@MONTANT 十进制(18,2)声明 tarif_cursor CURSOR FORSELECT CODE_TARIF、ZONE、UNITE、LIBELLE、TR_DEBUT、TR_FIN、MONTANTFROM T_TARIF打开 tarif_cursor;从 tarif_cursor 获取下一个进入@CODE_TARIF、@ZONE、@UNITE、@LIBELLE、@TR_DEB、@TR_FIN、@MONTANT；而@@FETCH_STATUS = 0开始插入 [T_TARIF]([CODE_TARIF]，[区]，[团结],[MODE_LIV],[诽谤],[TR_DEBUT],[TR_FIN],[蒙得])价值观(@CODE_TARIF，@区，@团结,@MODE_LIV,@LIBELLE,@TR_DEB,@TR_FIN,@蒙特)从 tarif_cursor 获取下一个进入@CODE_TARIF、@ZONE、@UNITE、@LIBELLE、@TR_DEB、@TR_FIN、@MONTANT；结尾结尾

它有效，但出现错误，请参阅视频:奇怪的存储过程

提前谢谢你，史蒂夫

解决方案

猜中...

您正在使用 ISO 游标语法.这默认为 not INSENSITIVE .这意味着当你插入行时，当你获取你刚刚插入的行等时.

来自声明光标

<块引用>

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

定义一个游标，该游标制作要由游标使用的数据的临时副本.对游标的所有请求都从 tempdb 中的这个临时表得到响应；因此，对基表所做的修改不会反映在对该游标进行的提取返回的数据中，并且该游标不允许修改.当使用 ISO 语法时，如果省略 INSENSITIVE，提交的删除和对基础表(由任何用户)进行的更新将反映在后续提取中.

无论如何，您只需要:无需循环

 INSERT INTO [T_TARIF]([CODE_TARIF]，[区]，[团结],[MODE_LIV],[诽谤],[TR_DEBUT],[TR_FIN],[蒙得])选择[CODE_TARIF]，[区]，[团结],@MODE_LIV,[诽谤],[TR_DEBUT],[TR_FIN],[蒙太奇]从[T_TARIF]

I have table with 3 primary keys : CODE_TARIF, UNITE, and MODE_LIV.

I write a stored procedure to copy and paste but with different MODE_LIV.

ex: if I already have 2 rows in table T_TARIF with MODE_LIV = 2, when I run this stored procedure with input MODE_LIV =3, I will have 4 rows .

ALTER PROCEDURE [dbo].[Copy_Tarif]
    -- Add the parameters for the stored procedure here
  @MODE_LIV int
AS
BEGIN
    DECLARE @CODE_TARIF varchar(15)
    DECLARE @ZONE int
    DECLARE @UNITE int
    DECLARE @LIBELLE varchar(30)

    DECLARE @TR_DEB int
    DECLARE @TR_FIN int
    DECLARE @MONTANT decimal(18,2)


    DECLARE tarif_cursor CURSOR FOR     
    SELECT CODE_TARIF, ZONE, UNITE, LIBELLE, TR_DEBUT, TR_FIN, MONTANT
    FROM T_TARIF

    OPEN tarif_cursor;

    FETCH NEXT FROM tarif_cursor 
    INTO  @CODE_TARIF, @ZONE, @UNITE, @LIBELLE,  @TR_DEB, @TR_FIN, @MONTANT;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO [T_TARIF]
           ([CODE_TARIF]
           ,[ZONE]
           ,[UNITE] 
           ,[MODE_LIV]         
           ,[LIBELLE]
           ,[TR_DEBUT]
           ,[TR_FIN]
           ,[MONTANT]

           )
     VALUES
           (@CODE_TARIF
           ,@ZONE
           ,@UNITE   
           ,@MODE_LIV       
           ,@LIBELLE
           ,@TR_DEB
           ,@TR_FIN
           ,@MONTANT

           )

        FETCH NEXT FROM tarif_cursor
        INTO  @CODE_TARIF, @ZONE, @UNITE, @LIBELLE,  @TR_DEB, @TR_FIN, @MONTANT;



    END
END

It works, but gives an error see Video : Strange Stored Procedure

Thanks you in advance, Stev

解决方案

Guessing...

You are using ISO cursor syntax. This defaults to not INSENSITIVE . Which means as you insert rows then when you FETCH you get rows you have just inserted etc etc.

From DECLARE CURSOR

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. When ISO syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.

In any event, all you need is this: No need to loop

 INSERT INTO [T_TARIF]
       ([CODE_TARIF]
       ,[ZONE]
       ,[UNITE] 
       ,[MODE_LIV]         
       ,[LIBELLE]
       ,[TR_DEBUT]
       ,[TR_FIN]
       ,[MONTANT])
 SELECT 
       [CODE_TARIF]
       ,[ZONE]
       ,[UNITE] 
       ,@MODE_LIV       
       ,[LIBELLE]
       ,[TR_DEBUT]
       ,[TR_FIN]
       ,[MONTANT]
 FROM
      [T_TARIF]
      

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