Transformer des bouts de codes vb en fonctions ou procédures sql server

Je sais que vous n'avez pas le temps , mais j'ai un petit problème à vous poser , j'ai une fonction écrite en vb 6.0 que j'aimerai transformer en ps ou fonction  sql server selon le cas le plus adapté.

J'ai lu l'article qui parlais d'éviter les curseurs , mais je me demande comment faire:

Je me permet de vous mettre ici le code vb que si vous avez le temps ou la patience vs regarderez, sinon ce n'est pas grave .
Je suis entrain d'essayer de le faire moi même petit à petit, mais un coup de mais serai le bien venu.
C'est une partie de fonction permettant de calculer le cump.

Merci pour votre compréhension et votre aide même minime.
Voici le bout de code:

Public Function CalculVeille(DateVeille As Date, Optional ArticleDeDebut As String, Optional ArticleDeFin As String) As Boolean

Dim DateFinVeille As Date
CalculVeille = False


ActiveBase.Execute "DELETE * FROM VALEUR"
If ArticleDeDebut = "" Then

ActiveBase.Execute "INSERT INTO VALEUR ( CODEMVT, CODPROD, QTE, PRIXUNIT, MONTANT, DEMANDEUR, DESTINATION, ORIGINE, UTILISATION, NUMOBJET, NUMFABRIC, CODEMACH, CUMP, VALEUR, QTEDISP,TYPEMVT,DATEMVT,CODEFAMIL,CODESFAM,BONENTRESORTIE) SELECT DETAILMOUVEMENTS.CODEMVT, DETAILMOUVEMENTS.CODPROD, DETAILMOUVEMENTS.QTE, DETAILMOUVEMENTS.PRIXUNIT, DETAILMOUVEMENTS.MONTANT, DETAILMOUVEMENTS.DEMANDEUR, DETAILMOUVEMENTS.DESTINATION, DETAILMOUVEMENTS.ORIGINE, DETAILMOUVEMENTS.UTILISATION, DETAILMOUVEMENTS.NUMOBJET, DETAILMOUVEMENTS.NUMFABRIC, DETAILMOUVEMENTS.CODEMACH, DETAILMOUVEMENTS.CUMP, DETAILMOUVEMENTS.VALEUR, DETAILMOUVEMENTS.QTEDISP, MOUVEMENTS.TYPEMVT, MOUVEMENTS.DATEMVT, ARTICLES.CODEFAMIL, ARTICLES.CODESFAM,MOUVEMENTS.BONENTRESORTIE " & _
"FROM (MOUVEMENTS INNER JOIN DETAILMOUVEMENTS ON MOUVEMENTS.CODEMVT = DETAILMOUVEMENTS.CODEMVT) INNER JOIN ARTICLES ON DETAILMOUVEMENTS.CODPROD = ARTICLES.CODPROD " & _
"WHERE (MOUVEMENTS.DATEMVT)>=#" & DebutExo & "# And (MOUVEMENTS.DATEMVT)<#" & DateVeille & "#"
Else
ActiveBase.Execute "INSERT INTO VALEUR ( CODEMVT, CODPROD, QTE, PRIXUNIT, MONTANT, DEMANDEUR, DESTINATION, ORIGINE, UTILISATION, NUMOBJET, NUMFABRIC, CODEMACH, CUMP, VALEUR, QTEDISP,TYPEMVT,DATEMVT,CODEFAMIL,CODESFAM,BONENTRESORTIE) SELECT DETAILMOUVEMENTS.CODEMVT, DETAILMOUVEMENTS.CODPROD, DETAILMOUVEMENTS.QTE, DETAILMOUVEMENTS.PRIXUNIT, DETAILMOUVEMENTS.MONTANT, DETAILMOUVEMENTS.DEMANDEUR, DETAILMOUVEMENTS.DESTINATION, DETAILMOUVEMENTS.ORIGINE, DETAILMOUVEMENTS.UTILISATION, DETAILMOUVEMENTS.NUMOBJET, DETAILMOUVEMENTS.NUMFABRIC, DETAILMOUVEMENTS.CODEMACH, DETAILMOUVEMENTS.CUMP, DETAILMOUVEMENTS.VALEUR, DETAILMOUVEMENTS.QTEDISP, MOUVEMENTS.TYPEMVT, MOUVEMENTS.DATEMVT, ARTICLES.CODEFAMIL, ARTICLES.CODESFAM,MOUVEMENTS.BONENTRESORTIE " & _
"FROM (MOUVEMENTS INNER JOIN DETAILMOUVEMENTS ON MOUVEMENTS.CODEMVT = DETAILMOUVEMENTS.CODEMVT) INNER JOIN ARTICLES ON DETAILMOUVEMENTS.CODPROD = ARTICLES.CODPROD " & _
"WHERE (MOUVEMENTS.DATEMVT)>=#" & DebutExo & "# And (MOUVEMENTS.DATEMVT)<#" & DateVeille & "# and DETAILMOUVEMENTS.CODPROD>='" & ArticleDeDebut & "' and DETAILMOUVEMENTS.CODPROD<='" & ArticleDeFin & "'"

End If


Set RstM_Valeur = New ADODB.Recordset
RstM_Valeur.Open "select * from Valeur order by codprod,datemvt,typemvt", ActiveBase, adOpenDynamic, adLockOptimistic


If RstM_Valeur.EOF Then 'fo continuer le traitement meme s'il y a pas de mouvements pour la période de veille
End If
First = True
If Not RstM_Valeur.EOF Then RstM_Valeur.MoveFirst: Cdd = RstM_Valeur.Fields("codprod")

While Not RstM_Valeur.EOF

While RstM_Valeur.Fields("codprod") = Cdd
If Not First Then
If RstM_Valeur.Fields("typemvt") = "E" Then
Ccump = (LastQte * LastCump + (RstM_Valeur.Fields("prixunit") * RstM_Valeur.Fields("qte"))) / (LastQte + RstM_Valeur.Fields("qte"))
LastQte = LastQte + RstM_Valeur.Fields("qte")
LastCump = Ccump
Else
LastCump = Ccump
LastQte = LastQte - RstM_Valeur.Fields("qte")
End If

RstM_Valeur.Fields("cump") = Round(LastCump, 2)
RstM_Valeur.Fields("qtedisp") = LastQte
RstM_Valeur.Update

End If
If First Then
First = False

UneCommande.ActiveConnection = ActiveBase
UneCommande.CommandType = adCmdStoredProc
UneCommande.CommandText = "PS_EXISTENCE_ARTICLE"
Set mParam = New ADODB.Parameter
Set mParam = UneCommande.CreateParameter("@Article", adChar, adParamInput, 9, Trim(Cdd))
UneCommande.Parameters.Append mParam
Set rst = New ADODB.Recordset
Set rst = UneCommande.Execute

If Not rst.EOF Then
'If Not RstArticles.EOF Then
'Pui = RstArticles!PRIXUNIT
'Qti = RstArticles!qteIni
Pui = rst!PRIXUNIT
Qti = rst!qteIni
Else
Pui = 0
Qti = 0
End If
If (Qti + RstM_Valeur.Fields("qte")) <> 0 Then
If RstM_Valeur.Fields("typemvt") = "S" Then
Ccump = Pui
LastQte = Qti - RstM_Valeur.Fields("qte")
Else
Ccump = ((Pui * Qti) + (RstM_Valeur.Fields("prixunit") * RstM_Valeur.Fields("qte"))) / (Qti + RstM_Valeur.Fields("qte"))
LastQte = Qti + RstM_Valeur.Fields("qte")
End If
Else
Ccump = 0
End If
LastCump = Ccump

RstM_Valeur.Fields("cump") = Round(LastCump, 2)
RstM_Valeur.Fields("qtedisp") = LastQte
RstM_Valeur.Update



End If
RstM_Valeur.MoveNext: If RstM_Valeur.EOF Then GoTo fin
Wend
First = True
Cdd = RstM_Valeur.Fields("codprod")
Wend

fin:
CalculVeille = True
If ArticleDeDebut = "" Then Call CumulStk1 Else Call CumulStk1(ArticleDeDebut, ArticleDeFin): Exit Function
Call CumGene1

End Function

VISSO

Je réponds moi même à mon problème :

Voici le code complet de la fonction en sql . sans utilisation de  curseur.

Enfin pour ce que cela vaut , j'espère que cela vaut quelque chose.

Merci pour votre aide.


CREATE PROCEDURE PS_CALCUL_VEILLE_QS(@DateVeille datetime,
                                                                          @ArticleDeDebut nvarchar(50), 
                                                                          @ArticleDeFin nvarchar(50),@DebutExo datetime)
 AS       
        
BEGIN     --1
 DECLARE   @NomTable nvarchar(50)
 DECLARE   @Cdd nvarchar(50)
             DECLARE   @ExistProd nvarchar(50)
 DECLARE   @First  bit
 DECLARE   @Ccump numeric
 DECLARE   @LastQte numeric
 DECLARE   @LastCump numeric
             DECLARE   @TypMvt   char(1)
             DECLARE   @PrixUnit numeric
             DECLARE   @PrixUnitArt numeric
             DECLARE   @LaQte numeric
             DECLARE   @LaQteIni numeric
             DECLARE   @Row_Id  nvarchar(1000)
             DECLARE   @QteDispo numeric
--DECLARE   @Ccump numeric 

                   SET @NomTable='#TEMP_VALEUR' 
                  IF EXISTS(SELECT * FROM   INFORMATION_SCHEMA.tables WHERE  TABLE_NAME = @NomTable)                               
                                  DROP TABLE   #TEMP_VALEUR
                              ELSE
                     BEGIN  --2
                                                      
                                     SELECT * , 0 AS OK
            INTO #TEMP_VALEUR
            FROM VALEUR ;  
                                  
                                   --  ALTER TABLE  #TEMP_VALEUR   ADD NBRE_LIGNE  nvarchar(1000) ;
                                 
                                     DELETE FROM #TEMP_VALEUR;                                
                   END   --2
                             
 
  IF @ArticleDeDebut = ''
                                
        BEGIN  --3
                                          
   INSERT INTO #TEMP_VALEUR ( CODEMVT, CODPROD, QTE, PRIXUNIT, MONTANT, DEMANDEUR, DESTINATION, ORIGINE, UTILISATION,
                                                                           NUMOBJET, NUMFABRIC, CODEMACH, CUMP, VALEUR, QTEDISP,TYPEMVT,DATEMVT,CODEFAMIL,
                                                                           CODESFAM,BONENTRESORTIE,CLEF_INFO,OK) SELECT  DETAILMOUVEMENTS.CODEMVT, DETAILMOUVEMENTS.CODPROD,
                                                                           DETAILMOUVEMENTS.QTE, DETAILMOUVEMENTS.PRIXUNIT, DETAILMOUVEMENTS.MONTANT,
                                                                           DETAILMOUVEMENTS.DEMANDEUR, DETAILMOUVEMENTS.DESTINATION, DETAILMOUVEMENTS.ORIGINE,
                                                                           DETAILMOUVEMENTS.UTILISATION, DETAILMOUVEMENTS.NUMOBJET, DETAILMOUVEMENTS.NUMFABRIC,
                                                                           DETAILMOUVEMENTS.CODEMACH, DETAILMOUVEMENTS.CUMP, DETAILMOUVEMENTS.VALEUR,
                                                                           DETAILMOUVEMENTS.QTEDISP, MOUVEMENTS.TYPEMVT, MOUVEMENTS.DATEMVT, ARTICLES.CODEFAMIL,
                                                                           ARTICLES.CODESFAM,MOUVEMENTS.BONENTRESORTIE,DETAILMOUVEMENTS.CLEF_INFO,0
                     FROM (MOUVEMENTS INNER JOIN DETAILMOUVEMENTS ON MOUVEMENTS.CODEMVT = DETAILMOUVEMENTS.CODEMVT)
                     INNER JOIN ARTICLES ON DETAILMOUVEMENTS.CODPROD = ARTICLES.CODPROD
                     WHERE (MOUVEMENTS.DATEMVT)>=@DebutExo  And (MOUVEMENTS.DATEMVT)<@DateVeille  ORDER BY  DETAILMOUVEMENTS.CODPROD,MOUVEMENTS.DATEMVT,MOUVEMENTS.TYPEMVT
                                                             
        END  --3
                                 
 Else
                       BEGIN   --4
                                         
           INSERT INTO  #TEMP_VALEUR ( CODEMVT, CODPROD, QTE, PRIXUNIT, MONTANT, DEMANDEUR, DESTINATION, ORIGINE, UTILISATION,
                                                                                                    NUMOBJET, NUMFABRIC, CODEMACH, CUMP, VALEUR, QTEDISP,TYPEMVT,DATEMVT,CODEFAMIL,
                                                                            CODESFAM,BONENTRESORTIE,CLEF_INFO,OK) SELECT  DETAILMOUVEMENTS.CODEMVT, DETAILMOUVEMENTS.CODPROD,
                                                                                                DETAILMOUVEMENTS.QTE, DETAILMOUVEMENTS.PRIXUNIT, DETAILMOUVEMENTS.MONTANT,
                                                                            DETAILMOUVEMENTS.DEMANDEUR, DETAILMOUVEMENTS.DESTINATION, DETAILMOUVEMENTS.ORIGINE,
                                                                            DETAILMOUVEMENTS.UTILISATION, DETAILMOUVEMENTS.NUMOBJET, DETAILMOUVEMENTS.NUMFABRIC,
                                                                            DETAILMOUVEMENTS.CODEMACH, DETAILMOUVEMENTS.CUMP, DETAILMOUVEMENTS.VALEUR,
                                                                            DETAILMOUVEMENTS.QTEDISP, MOUVEMENTS.TYPEMVT, MOUVEMENTS.DATEMVT, ARTICLES.CODEFAMIL,
                                                                            ARTICLES.CODESFAM,MOUVEMENTS.BONENTRESORTIE ,DETAILMOUVEMENTS.CLEF_INFO,0
                     FROM (MOUVEMENTS INNER JOIN DETAILMOUVEMENTS ON MOUVEMENTS.CODEMVT = DETAILMOUVEMENTS.CODEMVT)
                     INNER JOIN ARTICLES ON DETAILMOUVEMENTS.CODPROD = ARTICLES.CODPROD
                      WHERE (MOUVEMENTS.DATEMVT)>=@DebutExo  And (MOUVEMENTS.DATEMVT)< @DateVeille  and
                      DETAILMOUVEMENTS.CODPROD>=@ArticleDeDebut  and DETAILMOUVEMENTS.CODPROD<=@ArticleDeFin ORDER BY  DETAILMOUVEMENTS.CODPROD,MOUVEMENTS.DATEMVT,MOUVEMENTS.TYPEMVT
                                                           
     END    --4

                       
                                                  

        SET @LastQte=0
        SET  @Ccump=0 
        SET @LastCump=0
        SET   @First=1

 

 

         WHILE EXISTS ( SELECT   TOP 100 PERCENT *    FROM   #TEMP_VALEUR   WHERE  OK = 0  ORDER BY  CODPROD,DATEMVT,TYPEMVT)      
     
        BEGIN  --5
                          SET   @Cdd= (SELECT   TOP 1   CODPROD   FROM   #TEMP_VALEUR   WHERE  OK = 0    ORDER BY  CODPROD,DATEMVT,TYPEMVT)

                   WHILE EXISTS(SELECT   TOP 100 PERCENT *    FROM   #TEMP_VALEUR   WHERE  OK = 0  AND CODPROD=@Cdd   ORDER BY  CODPROD,DATEMVT,TYPEMVT)
                            BEGIN  --6
                 IF @First=0      
                                 BEGIN  --7
                                               SET   @TypMvt= (SELECT   TOP 1   TYPEMVT   FROM   #TEMP_VALEUR   WHERE  OK = 0    ORDER BY  CODPROD,DATEMVT,TYPEMVT)
                                                           SET   @LaQte= (SELECT   TOP 1   QTE   FROM   #TEMP_VALEUR   WHERE  OK = 0    ORDER BY  CODPROD,DATEMVT,TYPEMVT)
                                                                                    SET   @PrixUnit= (SELECT   TOP 1  PRIXUNIT   FROM   #TEMP_VALEUR   WHERE  OK = 0    ORDER BY  CODPROD,DATEMVT,TYPEMVT)

                                             IF  @TypMvt='E'
                                                      BEGIN  --8
                                                            SET  @Ccump= (@LastQte * @LastCump + (@PrixUnit * @LaQte)) / (@LastQte + @LaQte)
                                                            SET  @LastQte=@LastQte + @LaQte
                                                            SET  @LastCump=@Ccump    
                                                       END     --8                                 
                                             ELSE   
                                                       BEGIN   --9              
                                                              SET  @LastCump=@Ccump
                                                              SET  @LastQte=@LastQte - @LaQte            
                                                       END  --9
                                                                                            BEGIN
   
                                                                                  SET @Row_Id= (SELECT   TOP  1   CLEF_INFO
                                                                                                                        FROM   #TEMP_VALEUR   WHERE  OK = 0  
                                                                                                                        ORDER BY  CODPROD,DATEMVT,TYPEMVT)    
                                                                
                                                                                                                      UPDATE   #TEMP_VALEUR
                                                                                                                       SET  Cump= Round(@LastCump, 2), QteDisp=@LastQte,OK=1
                                                                                                                       Where CLEF_INFO=@Row_Id  
                                                                                       
                                                                                
                                                                                           END             
                                                         END  --7
                
                                        ELSE
                                                                        BEGIN  --10
                                                                                     SET  @First=0
                                                                                     SET  @PrixUnitArt=  (SELECT PRIXUNIT FROM  ARTICLES WHERE CODPROD=@cdd)
                                                                                     SET  @LaQteIni =    (SELECT QTEINI FROM  ARTICLES WHERE CODPROD=@cdd)
                                                                                     
                                                                                     IF  (@LaQteIni + @LaQte) <> 0
                                                                                           BEGIN  --11
                                                                                            IF  @TypMvt='S'
                                                                                                   BEGIN  --12
                                                                                                     SET @Ccump = @PrixUnitArt
                                                                                                     SET @LastQte = @LaQteIni - @LaQte
                                                                                                   END   --12
                                                                                                  ELSE
                                                                                                               BEGIN  --13
                                                                                                     SET @Ccump = ((@PrixUnitArt * @LaQteIni) + (@PrixUnit * @LaQte)) / (@LaQteIni + @LaQte)
                                                                                                     SET @LastQte = @LaQteIni + @LaQte
                                                                                                    END  --13  
                                                                                           END  --11
                                                                                    ELSE
                                                                                           SET @Ccump = 0   
                                                                                      END  --10
                                                                                               SET @LastCump = @Ccump        
                             

                                                                                  SET @Row_Id= (SELECT   TOP  1   CLEF_INFO  
                                                                                                                        FROM   #TEMP_VALEUR   WHERE  OK = 0  
                                                                                                                        ORDER BY CODPROD,DATEMVT,TYPEMVT)    
                                                                
                                                                                                                       UPDATE   #TEMP_VALEUR
                                                                                                                       SET  Cump= Round(@LastCump, 2), QteDisp=@LastQte,OK=1
                                                                                                                       Where CLEF_INFO=@Row_Id  
                                                                                                          
                                                                                 
                                                                   
      
                                      END  --6
                                    
      
                               END  --5 
      
                 
SELECT * FROM #TEMP_VALEUR
END  --1
GO


Merci et je suis disponible pour plus d'explication.

VISSO