SQL stored procedure works but with errors

Question

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

Please describe the error. I don't particularly want to watch a video to work out what your question is asking! — Martin Smith
– Martin Smith, Commented Oct 21, 2011 at 10:42
Thanks you for responding, it inserted the data but return error vialotion primary key. — user609511
– user609511, Commented Oct 21, 2011 at 11:06
Your cursor SELECT's result set might include rows where MODE_LIV is already equal to the target value of @MODE_LIV. Or there might be rows with identical CODE_TARIF & UNITE but different MODE_LIV. So my point is, you should add some filtering to the cursor SELECT (or, better still, to the SELECT part of the INSERT statement as suggested by @gbn) — Andriy M
– Andriy M, Commented Oct 23, 2011 at 15:26

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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]

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 21, 2011 at 10:45

gbn

435k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

gbn Over a year ago

@user609511: you don't need a loop: SQL is designed to work with more that one row

user609511 Over a year ago

Thanks you for responding, i try with DECLARE tarif_cursor INSENSITIVE CURSOR FOR but still error. i need loop because i inserted more than 1 row. i don't know how ? i must select * from table and then where i put this to tmporary variable ?

user609511 Over a year ago

i try with no loop -> i got only 1 row inserted.

user609511 Over a year ago

i follow your answer but still got : Violation de la contrainte PRIMARY KEY 'PK_T_TARIF'

gbn Over a year ago

@user609511: of course. It means that the combination of CODE_TARIF, UNITE, and MODE_LIV already exists

Collectives™ on Stack Overflow

SQL stored procedure works but with errors

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related