Request with While in SQL Server does not work

Question

Here is my code:

CREATE TABLE IF NOT EXISTS Artfacture 
(
     N int(6) unsigned NOT NULL,
     Code varchar(50) NOT NULL,
     Ht Numeric NOT NULL 
)

INSERT INTO Artfacture (N, Code, Ht) 
VALUES ('1', '1', '10'), ('1', '2', '20'),('1', '3', '30');

CREATE TABLE IF NOT EXISTS Facture 
(
     N int(6) unsigned NOT NULL,
     Ht Numeric NOT NULL 
)

INSERT INTO Facture (N, Ht) 
VALUES ('1', '60');

CREATE TABLE IF NOT EXISTS Article 
(
     Code varchar(50) NOT NULL, 
     Famille varchar(50) NOT NULL 
)

INSERT INTO Article (Code, Famille) 
VALUES ('1', 'F1'), ('2', 'F2'), ('3', 'F3');

CREATE TABLE IF NOT EXISTS Farticle 
(
     Designation varchar(50) NOT NULL, 
     Compte varchar(50) NOT NULL 
)

INSERT INTO Farticle (Designation, Compte)  
VALUES ('F1', '700000'), ('F2', '710000'), ('F3', '720000');

CREATE TABLE IF NOT EXISTS Ecritures 
(
     Compte varchar(50) NOT NULL, 
     Ht numeric NOT NULL  
)

My request is a trigger where adding some new rows in Table Facture, it must add rows in Ecritures with the same numbers of rows of Artfacture

declare @piece as nvarchar(50), @code as nvarchar(50)
declare @rowCount int = 0, @currentRow int = 1
select @rowCount = count(*) from ArtFacture where N = @piece;

while (@currentRow <= @rowCount)
begin
    set @Code = (select code from ArtFacture where N = @piece)
    set @compte = (select Compte from Farticle where Designation = (select Famille from Article where code = @code))
    set @Ht = (select ht from ArtFacture where N = @piece)

    insert into Ecritures (Compte,Ht)
    values (@compte,@Ht)
end

I have a mistake but I do not know where?

you declare `@piece` to be `nvarchar(50)` and you never assign it a value.. so your `SELECT @rowCount = COUNT(*)FROM ArtFacture where N=@piece;` returns 0 every time i'm guessing. and your while statement will never be true as 1 is never less than or equal to 0 — austin wernli, May 26 '20 at 20:52
Does this answer your question? [Sql Server trigger insert values from new row into another table](https://stackoverflow.com/questions/2247679/sql-server-trigger-insert-values-from-new-row-into-another-table) — austin wernli, May 26 '20 at 20:58

Dale K · Answer 1 · 2020-05-26T23:37:20.413

0

You're making the classic mistake of trying to write procedural T-SQL instead of set-based T-SQL which SQL Server is optimised for.

If I follow your logic then the following insert removes the need for a while loop:

insert into Ecritures (Compte, Ht)
select F.Compte, AF.Ht
from Artfracture AF
inner join Article A on A.Code = AF.Code
inner join Farticle F on F.Designation = A.Famille
where N = @piece;

And if you don't want duplicates add a group by:

insert into Ecritures (Compte, Ht)
select F.Compte, AF.Ht
from Artfracture AF
inner join Article A on A.Code = AF.Code
inner join Farticle F on F.Designation = A.Famille
where N = @piece
group by F.Compte, AF.Ht;

edited May 26 '20 at 23:37

answered May 26 '20 at 21:01

Dale K

25,246
15
42
71

Thank you, it work but when i have tested and assign same "famille" to 2 "articles", he count is the sum of the 2 "ht", A1 with F1 and A2 with F1 again. – Mohammed FETTAH May 26 '20 at 21:36
Are you saying you are getting duplicate rows in Ecritures and you don't want them? – Dale K May 26 '20 at 21:41
Sample data and expected results would make clear exactly what you are asking. – Dale K May 26 '20 at 21:49

Request with While in SQL Server does not work

1 Answers1