The Weird and The Wonderful

The Weird and The Wonderful forum is a place to post Coding Horrors, Worst Practices, and the occasional flash of brilliance.

We all come across code that simply boggles the mind. Lazy kludges, embarrassing mistakes, horrid workarounds and developers just not quite getting it. And then somedays we come across - or write - the truly sublime.

Post your Best, your worst, and your most interesting. But please - no programming questions . This forum is purely for amusement and discussions on code snippets. All actual programming questions will be removed.

Re: This guy don't know what margin property in CSS is.

jsc4216-Jul-12 23:03

jsc42

16-Jul-12 23:03

At least he didn't use a single transparent pixel GIF with, for example

HTML

<img src="TransparentPixel1.gif" height="130">

Re: This guy don't know what margin property in CSS is.

_Amy18-Jul-12 0:15

_Amy

18-Jul-12 0:15

..Wow.!!! New margin style.. Laugh | :laugh:

I'll try it in .Net Framework-10.0 . Laugh | :laugh:

Read the article "Table Valued Parameters".

--Amit

Evolution of a stored proc

virang_2112-Jul-12 20:37

virang_21

12-Jul-12 20:37

I have few of those in one of the project I am responsible to maintain... and sometimes something goes wrong with the system and I have oh no .. not again moment....I am not saying stored proc are bad but at times it takes you on a wild ride when figuring out what went wrong and itis absolute nightmare to debug...

SQL

-- =============================================                        
-- Author: XXXX                    
-- Create date: <06/07/2009>                        
-- Description: <UPDATE MOrdNo>                        
-- =============================================                        
ALTER PROCEDURE [dbo].[uspUpdateMfgOrdNo]                      
AS                      
                      
BEGIN                      
 DECLARE @Machine_Id varchar(8)                      
 DECLARE @Flt_Locn VARCHAR(8)                      
 DECLARE @Prod_Date datetime                      
 DECLARE @Shift_Id VARCHAR(1)                      
 DECLARE @Item_No varchar(15)                      
 DECLARE @Prod_Qty numeric(9,0)                      
 DECLARE @Tot_Prod_Qty NUMERIC(9,0)                      
 DECLARE @Mh_Mfno varchar(8)                      
 DECLARE @Seq_Id NUMERIC(18,0)                      
 DECLARE @WorkCenter_Id VARCHAR(8)                      
 DECLARE @Facility_Id VARCHAR(8)                      
 DECLARE @Matched_MfNo VARCHAR(8)                      
 DECLARE @Available_Qty NUMERIC(9, 0)                      
 DECLARE @CurrentQty NUMERIC(9, 0)                      
 DECLARE @FilledQty NUMERIC(9, 0)                      
 --DECLARE @UpperLimit_Qty NUMERIC(9, 0)                      
 DECLARE @Cono NUMERIC(9, 0)                      
 DECLARE @Scrap_Qty  NUMERIC(9, 0)                      
 DECLARE @RowCount INT                       
 DECLARE @blFlagMultiMO CHAR(1)                      
 DECLARE @MachHistTemp TABLE(Id INT,Machine_Id VARCHAR(8),Flt_Locn VARCHAR(8),Prod_Date DATETIME,                      
   Shift_Id VARCHAR(1),Item_No VARCHAR(15),Prod_Qty NUMERIC(9,0),Mh_Mfno VARCHAR(8),                      
   Seq_Id NUMERIC(18,0))                      
 DECLARE @ROWCNT INT                      
 DECLARE @CNT  INT                      
                      
                        
 SET @blFlagMultiMO = 'F'                      
                       
 BEGIN TRAN                      
                       
 Branch_One:--DECLARATION OF THE BRANCH LABEL                      
                       
 TRUNCATE TABLE MachHistTemp                      
                      
 INSERT INTO MachHistTemp(Machine_Id,Flt_Locn,Prod_Date,Shift_Id,Item_No,Prod_Qty,Mh_Mfno,Seq_Id)                      
 SELECT Machine_Id,Flt_Locn,Prod_Date,Shift_Id,Item_No,Prod_Qty,Mh_Mfno,Seq_Id FROM MachHist WITh(NOLOCK)                      
 WHERE mh_mfno=-1 or mh_mfno=-2 order by mh_mfno DESC --mh_mfno IS NULL OR                      
                      
 SET @CNT=1                      
 SELECT @ROWCNT=MAX(Id) FROM MachHistTemp                      
                       
 WHILE(@CNT <= @ROWCNT)                      
 BEGIN                      
  SELECT @Machine_Id=Machine_Id,@Flt_Locn=Flt_Locn,@Prod_Date=Prod_Date,        
  @Shift_Id=Shift_Id,@Item_No=Item_No,@Prod_Qty=Prod_Qty, @Mh_Mfno=Mh_Mfno,        
  @Seq_Id=Seq_Id FROM MachHistTemp WHERE Id=@CNT                      
                         
  SET @CurrentQty = @Prod_Qty                      
  SET @Scrap_Qty = 0                      
                        
  -- Start Added IF condition on 4/Nov/2009 as suggested by Manager.                      
  IF (@Mh_Mfno = -2)                      
   BEGIN                      
    SET @Scrap_Qty = @Prod_Qty                      
   END                      
   -- End                      
                      
  SELECT @WorkCenter_Id=[Wc_Id] FROM [dbo].[Machine] WITh(NOLOCK) WHERE [Machine_Id] = @Machine_Id                      
  SELECT @Facility_Id = [Faci_Id] FROM dbo.WorkCenter WITh(NOLOCK) WHERE Wc_ID = @WorkCenter_Id                      
                   
  WHILE (@CurrentQty != 0)                      
   BEGIN                      
    -- Start Added on 21/Oct/2009 as suggested by Manager                      
    IF (@Facility_Id = 'C29') -- WETHERILL PARK                      
     BEGIN                      
      SELECT TOP 1 @Matched_MfNo=[Mhd_MfNo],                             @Available_Qty=ISNULL([Mhd_Orqt], 0) - ISNULL([Mhd_Maqa], 0),                      
      @Cono = [Mhd_Cono]                      
      FROM [dbo].[MOrdHdr] WITh(NOLOCK)                      
      -- has qty available to accomodate has is below upper limit                      
      --Changed the Where Clause on 18/November/2009 as asked by Manager                       
      --WHERE ( (ISNULL([Mhd_Maqa], 0) - ISNULL([Mhd_Scqa], 0)) < ISNULL([Mhd_Orqt], 0) )                         
      WHERE ( (ISNULL([Mhd_Maqa], 0)) < ISNULL([Mhd_Orqt], 0) )                         
      --AND  (ISNULL([Mhd_Maqa], 0) - ISNULL([Mhd_Scqa], 0)) >= 0 --extra check                        
      AND [Mhd_Prno]= @Item_No AND [Mhd_Faci]= @Facility_Id                     
      AND [Mhd_Whst] < 90 ---Added on 02/12/2009                     
      ORDER BY [Mhd_Stdt] ASC, [Mhd_Msti] ASC ,[Mhd_MfNo] ASC                     
     END                      
    ELSE IF (@Facility_Id = 'C59') -- WOOD                  
     BEGIN                      
      SELECT TOP 1 @Matched_MfNo=[Mhd_MfNo],                       
      --Changed the Aval Qty calculation on 18/November/2009 as asked by Manager                      
      --@Available_Qty=ISNULL([Mhd_Orqt], 0) - (ISNULL([Mhd_Maqa], 0) - ISNULL([Mhd_Scqa], 0)),                        
      @Available_Qty=ISNULL([Mhd_Orqt], 0) - ISNULL([Mhd_Maqa], 0),                      
      --@UpperLimit_Qty = ISNULL([Mhd_Orqt], 0),                      
      @Cono = [Mhd_Cono]                      
      FROM [dbo].[MOrdHdr] WITh(NOLOCK)                     
      -- has qty available to accomodate has is below upper limit                      
      --Changed the Where Clause on 18/November/2009 as asked by Manager                       
      --WHERE ( (ISNULL([Mhd_Maqa], 0) - ISNULL([Mhd_Scqa], 0)) < ISNULL([Mhd_Orqt], 0) )                         
      WHERE ( (ISNULL([Mhd_Maqa], 0)) < ISNULL([Mhd_Orqt], 0) )                         
      --AND  (ISNULL([Mhd_Maqa], 0) - ISNULL([Mhd_Scqa], 0)) >= 0 --extra check                        
      AND [Mhd_Prno]= @Item_No AND [Mhd_Faci]= @Facility_Id                      
      AND [Mhd_machine] = @Machine_Id -- Added condition on 21/Oct/2009 as suggested by Manager                     
      AND [Mhd_Whst] < 90 ---Added on 02/12/2009                      
      ORDER BY [Mhd_Stdt] ASC, [Mhd_Msti] ASC,[Mhd_MfNo] ASC                   
              
      Print @@ROWCOUNT              
      --Added on 30/03/2010 as per Manager's email                
      IF(@@ROWCOUNT = 0)                  
       BEGIN                
        SELECT TOP 1 @Matched_MfNo=[Mhd_MfNo],                       
        @Available_Qty=ISNULL([Mhd_Orqt], 0) - ISNULL([Mhd_Maqa], 0),                      
        @Cono = [Mhd_Cono]                      
        FROM [dbo].[MOrdHdr] WITh(NOLOCK)                        
        WHERE ( (ISNULL([Mhd_Maqa], 0)) < ISNULL([Mhd_Orqt], 0) )                         
        AND [Mhd_Prno]= @Item_No AND [Mhd_Faci]= @Facility_Id                      
        AND [Mhd_Whst] < 90                 
        ORDER BY [Mhd_Stdt] ASC, [Mhd_Msti] ASC ,[Mhd_MfNo] ASC                  
                      
          END                     
     ---------------------                
     END                      
    -- End                   
                    
                
    IF (@@ROWCOUNT = 0)                      
     BEGIN                      
      SET @CurrentQty = 0                         
     END                      
    ELSE                      
     BEGIN                      
      SET @CurrentQty = @Available_Qty - @CurrentQty                      
                    
       -- Start Code added on 16/Sept/2009                      
                      
      IF (@CurrentQty < 0)                       
       BEGIN                      
        -- This means that the required qty > available qty                      
        SET @FilledQty = @Available_Qty                      
        SET @CurrentQty = ABS(@CurrentQty)                      
        SET @blFlagMultiMO = 'T'                      
                      
        UPDATE [dbo].[MachHist]                      
        SET Mh_Mfno=@Matched_MfNo,                      
        Prod_Qty=@FilledQty                      
        WHERE [Seq_Id]=@Seq_Id                      
                     
        INSERT INTO [dbo].[MachHist](Machine_Id, Flt_Locn, Prod_Date, Shift_Id,            
        Item_No, Prod_Qty, Flt_Code, Mh_Mfno, Crt_date, Crt_time)                      
        VALUES (@Machine_Id, @Flt_Locn, @Prod_Date, @Shift_Id, @Item_No, @CurrentQty,             
        '', NULL, Getdate(), Getdate())                      
                            
        ---UPDATE MOrdHdr FOR maqa,scqa                      
        UPDATE [dbo].[MOrdHdr]                       
        SET [Mhd_Maqa]=ISNULL([Mhd_Maqa],0) + @FilledQty                      
        ,[Mhd_Scqa] = ISNULL([Mhd_Scqa],0) + @Scrap_Qty                 
        WHERE [Mhd_Mfno]=@Matched_MfNo         
        AND [Mhd_Prno]= @Item_No         
        AND [Mhd_Faci]= @Facility_Id                      
                                   
        -- Update the corresponding TOrdHdr record                      
        IF EXISTS (SELECT 1 FROM [dbo].[TOrdHdr] WITh(NOLOCK)                          
           WHERE [Mhd_cono]=@Cono         
           AND [Faci_id]=@Facility_Id         
           AND [Mhd_mfno]=@Matched_MfNo             
           AND [Mhd_prno]=@Item_No         
           AND [Sfdc_Sts] = 'O') -- Start Added AND condition on 14/Oct/2009 as suggested by Manager                      
         BEGIN                      
          UPDATE [dbo].[TOrdHdr]                       
          SET [Mhd_Maqa] = ISNULL([Mhd_Maqa],0) + @FilledQty                      
          ,[Mhd_Scqa] = ISNULL([Mhd_Scqa],0) + @Scrap_Qty                      
          WHERE [Mhd_cono]=@Cono           
          AND [Faci_id]=@Facility_Id           
          AND [Mhd_mfno]=@Matched_MfNo           
          AND [Mhd_prno]=@Item_No                  
          AND [Sfdc_Sts] = 'O' --Added on 24/Feb/2010                       
         END                       
        ELSE                      
         BEGIN                      
          INSERT INTO [dbo].[TOrdHdr](Mhd_cono, Faci_id, Mhd_mfno, Mhd_prno,           
          Mhd_whst, Mhd_orqt, Mhd_prio, Mhd_maqa,                       
          Mhd_scqa, Mhd_stdt, Mhd_msti, Mhd_fidt, Mhd_mfti,           
          Mhd_type, Mhd_locn, Mhd_rptno, Mhd_machine,Sfdc_Sts,        
          Mhd_Seqs , Mhd_Prap, Mhd_Schs, --Added on 24/06/2010                      
          Mhd_Plgf ) -- Added on  25/06/2010      
          SELECT Mhd_cono, Mhd_Faci, Mhd_mfno, Mhd_prno, Mhd_whst,         
          Mhd_orqt, Mhd_prio, @FilledQty,                       
          @Scrap_Qty, Mhd_stdt, Mhd_msti, Mhd_fidt, Mhd_mfti,          
          Mhd_type, Mhd_locn, Mhd_rptno, Mhd_machine,'O',        
          Mhd_Seqs , Mhd_Prap, Mhd_Schs,Mhd_Plgf                      
          FROM [dbo].[MOrdHdr] WITh(NOLOCK)                        
          WHERE [Mhd_Mfno] = @Matched_MfNo          
          AND [Mhd_Prno]= @Item_No         
          AND [Mhd_Faci]= @Facility_Id                         
         END                      
         -- Update the corresponding MOrdOpr record                      
         IF EXISTS (SELECT 1 FROM [dbo].[MOrdOpr] WITh(NOLOCK)                        
             WHERE [Opr_Cono] = @Cono           
             AND [Opr_Faci] = @Facility_Id           
             AND [Opr_Mfno] = @Matched_MfNo                      
             AND [Opr_Prno] = @Item_No)                      
          BEGIN                      
              UPDATE [dbo].[MOrdOpr]                      
              SET [Opr_Maqa] = ISNULL([Opr_Maqa],0) + @FilledQty                      
              ,[Opr_Scqa] = ISNULL([Opr_Scqa],0) + @Scrap_Qty                      
              WHERE [Opr_Cono] = @Cono         
              AND [Opr_Faci] = @Facility_Id           
              AND [Opr_Mfno] = @Matched_MfNo                      
              AND [Opr_Prno] = @Item_No                      
          END -- If a record in MOrdHdr exists, a corresponding record will exist in MOrdOpr                      
                           
          -- Update the corresponding record in TOrdOpr                      
         IF EXISTS (SELECT 1 FROM [dbo].[TOrdOpr] WITh(NOLOCK)                         
             WHERE [Opr_Cono] = @Cono           
             AND [Faci_id] = @Facility_Id           
             AND [Opr_Mfno] = @Matched_MfNo                      
             AND [Opr_Prno] = @Item_No                      
             AND [Sfdc_Sts] = 'O') -- Start Added AND condition on 14/Oct/2009 as suggested by Manager                      
          BEGIN                      
              UPDATE [dbo].[TOrdOpr]                       
              SET [Opr_Maqa] = ISNULL([Opr_Maqa],0) + @FilledQty                      
              ,[Opr_Scqa] = ISNULL([Opr_Scqa],0) + @Scrap_Qty                      
              WHERE [Opr_Cono] = @Cono         
              AND [Faci_id] = @Facility_Id         
              AND [Opr_Mfno] = @Matched_MfNo                      
              AND [Opr_Prno] = @Item_No           
              AND [Sfdc_Sts] = 'O' --Added on 24/Feb/2010                      
          END                       
         ELSE                      
          BEGIN                      
              INSERT INTO [dbo].[TOrdOpr](Opr_cono, Faci_id,           
              Opr_mfno, Opr_prno, Opr_wost, Opr_opno, Opr_opds, Opr_orqt,                      
              Opr_maqa, Opr_scqa, Opr_stdt, Opr_msti, Opr_wcetre,           
              Opr_subc_cf, Opr_Conto, Opr_rptno,Sfdc_Sts)                      
              SELECT Opr_cono, Opr_Faci, Opr_mfno, Opr_prno, Opr_wost, Opr_opno,         
              Opr_opds, Opr_orqt, @FilledQty, @Scrap_Qty, Opr_stdt, Opr_msti,         
              Opr_wcentre, Opr_subc_cf, Opr_Conto, Opr_rptno,'O'                      
              FROM [dbo].[MOrdOpr] WITh(NOLOCK)                        
              WHERE [Opr_Cono] = @Cono         
              AND [Opr_Faci] = @Facility_Id         
              AND [Opr_Mfno] = @Matched_MfNo                      
              AND [Opr_Prno] = @Item_No                          
          END                      
                          
         SET @CurrentQty = 0 --break out of the loop                      
                             
         GOTO Branch_One                       
                            
   END                       
  ELSE                       
   BEGIN                      
    -- This means that the required qty < available qty                
                       
    IF @blFlagMultiMO = 'T'                      
     BEGIN                      
      SET @FilledQty = ABS(@Available_Qty - @CurrentQty) --Commented on 16/Sept/2009                      
      SET @blFlagMultiMO = 'F'                      
     END                      
    ELSE                      
     SET @FilledQty = ABS(@Prod_Qty) --Uncommented on 16/Sept/2009                      
                    
    -- Start Added IF condition on 4/Nov/2009 as suggested by Manager.                      
    IF (@Mh_Mfno = -2)                      
     BEGIN                      
      SET @Scrap_Qty = @Prod_Qty                      
     END           
    ELSE          
     BEGIN          
      SET @Scrap_Qty = 0          
     END                     
    -- End             
          
          
    UPDATE [dbo].[MachHist]                      
    SET Mh_Mfno=@Matched_MfNo                      
    WHERE [Seq_Id]=@Seq_Id                      
                                
                -- added
    BEGIN  
     IF @Scrap_Qty  > @FilledQty   
      declare @currDate varchar(10)   
      declare @errDesc varchar(100)  
  
      Set @currDate= Convert(VARCHAR(10),GetDate(),103)  
      --Set @errDesc = 'MO: ' + @Matched_MfNo + ' - ' +(@FilledQty) + ' - ' + (@Scrap_Qty)  -- modified by charu on 07122011 as it was causing an error
       Set @errDesc = 'MO: ' + CONVERT(varchar,@Matched_MfNo) + ' - ' + CONVERT(varchar,@FilledQty) + ' - ' +  CONVERT(varchar,@Scrap_Qty)    
      exec uspSaveAPIErr @Facility_Id, @currDate, 'uspUpdateMfgOrdNo', 'err', 'qty error', @errDesc , '10' 

    END  
    ---UPDATE MOrdHdr FOR maqa,scqa                      
    UPDATE [dbo].[MOrdHdr]                       
    SET [Mhd_Maqa]=ISNULL([Mhd_Maqa],0) + @FilledQty                      
    ,[Mhd_Scqa] = ISNULL([Mhd_Scqa],0) + @Scrap_Qty                      
    WHERE [Mhd_Mfno]=@Matched_MfNo         
    AND [Mhd_Prno]= @Item_No         
    AND [Mhd_Faci]= @Facility_Id                      
                                   
    -- Update the corresponding TOrdHdr record                      
    IF EXISTS ( SELECT 1 FROM [dbo].[TOrdHdr] WITh(NOLOCK)                         
        WHERE [Mhd_cono]=@Cono         
        AND [Faci_id]=@Facility_Id           
        AND [Mhd_mfno]=@Matched_MfNo         
        AND [Mhd_prno]=@Item_No                      
        AND [Sfdc_Sts] = 'O') -- Start Added AND condition on 14/Oct/2009 as suggested by Manager                      
     BEGIN                      
        UPDATE [dbo].[TOrdHdr]                       
        SET [Mhd_Maqa] = ISNULL([Mhd_Maqa],0) + @FilledQty                      
        ,[Mhd_Scqa] = ISNULL([Mhd_Scqa],0) + @Scrap_Qty                      
        WHERE [Mhd_cono]=@Cono         
        AND [Faci_id]=@Facility_Id           
        AND [Mhd_mfno]=@Matched_MfNo         
        AND [Mhd_prno]=@Item_No                  
        AND [Sfdc_Sts] = 'O' --Added on 24/Feb/2010                       
     END                       
    ELSE                      
     BEGIN                      
        INSERT INTO [dbo].[TOrdHdr](Mhd_cono, Faci_id, Mhd_mfno,           
        Mhd_prno, Mhd_whst, Mhd_orqt, Mhd_prio, Mhd_maqa,                       
        Mhd_scqa, Mhd_stdt, Mhd_msti, Mhd_fidt, Mhd_mfti, Mhd_type,           
        Mhd_locn, Mhd_rptno, Mhd_machine,Sfdc_Sts,        
        Mhd_Seqs , Mhd_Prap, Mhd_Schs,--Added on 24/06/2010           
      Mhd_Plgf) -- Added on 25/06/2010      
        SELECT Mhd_cono, Mhd_Faci, Mhd_mfno, Mhd_prno, Mhd_whst,           
        Mhd_orqt, Mhd_prio, @FilledQty,                       
        @Scrap_Qty, Mhd_stdt, Mhd_msti, Mhd_fidt, Mhd_mfti,          
        Mhd_type, Mhd_locn, Mhd_rptno, Mhd_machine,'O',        
        Mhd_Seqs , Mhd_Prap, Mhd_Schs,Mhd_Plgf                      
        FROM [dbo].[MOrdHdr] WITh(NOLOCK)                        
        WHERE [Mhd_Mfno] = @Matched_MfNo          
        AND [Mhd_Prno]= @Item_No         
        AND [Mhd_Faci]= @Facility_Id                         
     END                      
    -- Update the corresponding MOrdOpr record                      
    IF EXISTS ( SELECT 1 FROM [dbo].[MOrdOpr] WITh(NOLOCK)                         
       WHERE [Opr_Cono] = @Cono           
       AND [Opr_Faci] = @Facility_Id         
       AND [Opr_Mfno] = @Matched_MfNo                      
       AND [Opr_Prno] = @Item_No)                      
     BEGIN                      
      UPDATE [dbo].[MOrdOpr]                      
      SET [Opr_Maqa] = ISNULL([Opr_Maqa],0) + @FilledQty                      
      ,[Opr_Scqa] = ISNULL([Opr_Scqa],0) + @Scrap_Qty                      
      WHERE [Opr_Cono] = @Cono         
      AND [Opr_Faci] = @Facility_Id         
      AND [Opr_Mfno] = @Matched_MfNo                      
      AND [Opr_Prno] = @Item_No                      
     END -- If a record in MOrdHdr exists, a corresponding record will exist in MOrdOpr                      
                       
    -- Update the corresponding record in TOrdOpr                      
    IF EXISTS (SELECT 1 FROM [dbo].[TOrdOpr] WITh(NOLOCK)                          
        WHERE [Opr_Cono] = @Cono         
        AND [Faci_id] = @Facility_Id           
        AND [Opr_Mfno] = @Matched_MfNo                      
        AND [Opr_Prno] = @Item_No                      
        AND [Sfdc_Sts] = 'O') -- Start Added AND condition on 14/Oct/2009 as suggested by Manager                      
     BEGIN                      
        UPDATE [dbo].[TOrdOpr]                       
        SET [Opr_Maqa] = ISNULL([Opr_Maqa],0) + @FilledQty                      
        ,[Opr_Scqa] = ISNULL([Opr_Scqa],0) + @Scrap_Qty                      
        WHERE [Opr_Cono] = @Cono         
        AND [Faci_id] = @Facility_Id         
        AND [Opr_Mfno] = @Matched_MfNo                      
        AND [Opr_Prno] = @Item_No          
        AND [Sfdc_Sts] = 'O' --Added on 24/Feb/2010                     
     END                       
    ELSE                      
      BEGIN                      
        INSERT INTO [dbo].[TOrdOpr](Opr_cono, Faci_id, Opr_mfno,        
      Opr_prno, Opr_wost, Opr_opno, Opr_opds, Opr_orqt,                      
        Opr_maqa, Opr_scqa, Opr_stdt, Opr_msti, Opr_wcetre, Opr_subc_cf,         
        Opr_Conto, Opr_rptno,Sfdc_Sts)                      
        SELECT Opr_cono, Opr_Faci, Opr_mfno, Opr_prno, Opr_wost, Opr_opno,         
        Opr_opds, Opr_orqt,@FilledQty, @Scrap_Qty, Opr_stdt, Opr_msti,         
        Opr_wcentre, Opr_subc_cf, Opr_Conto, Opr_rptno,'O'                      
        FROM [dbo].[MOrdOpr] WITh(NOLOCK)                        
        WHERE [Opr_Cono] = @Cono         
        AND [Opr_Faci] = @Facility_Id         
        AND [Opr_Mfno] = @Matched_MfNo                      
        AND [Opr_Prno] = @Item_No                          
      END                  
                        
    SET @CurrentQty = 0 --break out of the loop                      
                            
   END                      
      END                       
    END                      
                      
   SET @CNT=@CNT + 1                      
  END--END OF WHILE LOOP                      
 COMMIT TRAN                      
END

Zen and the art of software maintenance : rm -rf *

Math is like love : a simple idea but it can get complicated.

Re: Evolution of a stored proc

BobJanova12-Jul-12 23:23

BobJanova

12-Jul-12 23:23

Re: Evolution of a stored proc

Mike Winiberg16-Jul-12 20:14

Mike Winiberg

16-Jul-12 20:14

Hmm,

I will never complain about Visual Basic again...

Laugh | :laugh:

Re: Evolution of a stored proc

AspDotNetDev16-Jul-12 20:45

AspDotNetDev

16-Jul-12 20:45

Good lord, I'm having flashbacks from my last job. We had nearly 500,000 lines of SQL in stored procedures. And I really don't see the need for the "usp" prefix. Is it really so common to confuse a user stored procedure with a system stored procedure?

At least it seems the developers that made this attempt to maintain some sort of style. At my current job, most stored procedures look like they were generated from a designer and no attempt was made to format the code.

I feel your pain.

Thou mewling ill-breeding pignut!

Re: Evolution of a stored proc

greldak16-Jul-12 21:43

greldak

16-Jul-12 21:43

Hmm - lots of comments giving the reason for a change as "as suggested by manager". Doesn't inspire confidence that the person making those changes understood why they were done.

Re: Evolution of a stored proc

Stefan_Lang16-Jul-12 21:55

Stefan_Lang

16-Jul-12 21:55

Yes, I particularly like the addition:

SQL

-- Start Added IF condition on 4/Nov/2009 as suggested by Manager.                      
  IF (@Mh_Mfno = -2)                      
   BEGIN                      
    SET @Scrap_Qty = @Prod_Qty                      
   END                      
   -- End

which to me sounds like

"if I press the red button, dump all produce on the garbage heap"

P.S.: I wonder if one of the 'M's in Mh_Mfno stands for 'Manager' Wink | ;)

Re: Evolution of a stored proc

yiangos16-Jul-12 22:43

yiangos

16-Jul-12 22:43

virang_21 wrote:
BEGIN TRAN

Branch_One:--DECLARATION OF THE BRANCH LABEL

TRUNCATE TABLE MachHistTemp

I stopped reading the code after this.

A DDL statement within a transaction?

Φευ! Εδόμεθα υπό ρηννοσχήμων λύκων!
(Alas! We're devoured by lamb-guised wolves!)

Re: Evolution of a stored proc

Jan Holst Jensen217-Jul-12 1:01

Jan Holst Jensen2

17-Jul-12 1:01

Actually, truncate _can_ be rolled back on SQL server, so it is not as bad as it would be if it were Oracle code.

Re: Evolution of a stored proc

yiangos17-Jul-12 9:16

yiangos

17-Jul-12 9:16

Ah, yes, you're quite right on that one. Blush | :O

Φευ! Εδόμεθα υπό ρηννοσχήμων λύκων!
(Alas! We're devoured by lamb-guised wolves!)

Re: Evolution of a stored proc

Florin Jurcovici17-Jul-12 1:31

Florin Jurcovici

17-Jul-12 1:31

You can refactor it. Also, I don't know for Oracle or other DBs, but there's a debugger with breakpoints, local vars, step into and all such stuff available for MySQL.

OTOH, packing business logic into SQL (which I'm under the impression that it happens here) is always a bad idea - unless you really do pack your entire logic in SQL, and have stored procedures which are in fact the equivalent of services, and no or almost no logic in your client app.

And yes, I do agree it's horrible the way it is now. Whoever designed the database clearly has no notion of sane naming conventions.

Re: Evolution of a stored proc

Harley L. Pebley17-Jul-12 4:47

Harley L. Pebley

17-Jul-12 4:47

virang_21 wrote:
I am not saying stored proc are bad

I'm no DB expert, but stored procedures always seem to violate the "do one thing and do it well" principle. Seems to me, DBs should, I don't know, store data. Acting on that data seems to go beyond their purview.

About the only case I can think of where an SP might be appropriate is to synthesize read-only data from other stored data.

Re: Evolution of a stored proc

krumia17-Jul-12 20:20

krumia

17-Jul-12 20:20

The size of the proc is scary enough, but here's the scariest thing in my opinion.

Added IF condition on 4/Nov/2009 as suggested by Manager.
Changed the Where Clause on 18/November/2009 as asked by Manager

Really? What do system analysts do in this company? D'Oh! | :doh:

When to check for null ?

Sasha Laurel10-Jul-12 6:19

Sasha Laurel

10-Jul-12 6:19

Another project from our "star" developer who is apparently beyond contempt in every way.

I was given this website (an ASP.Net Webforms project written in VB10) to add some additional metrics. To my horror I found that even though written very recently (within the last 2-3 years) and then updated to use a new Linq-to-Entities data source, there was no regard for architecture, and all of the data access code was written directly in the code behind of the aspx pages.

That's not even the real horror. I was getting (seemingly) random errors while trying to run it/understand it, and found that through-out all of the loads of Linq statements that all of the nullable types (e.g. Integer?, Double?, DateTime?, etc.) were being treated as if they were not nullable at all!

Here is an example to help you understand (table/field names changed slightly to protect the guilty):

Dim partTransactionTypeThingsQuery As IQueryable(Of SomeTransactionRelatedItem) = _
            From t In baseQuery
            Join tji In dataContext.OtherTransactionItems _
                On tji.SomeTransactionID Equals t.ID
            Where (t.LastUpdatedDate >= startDate _
                   And t.LastUpdatedDate < endDate) _
                And (tji.Type = "Part") _
                And (tji.TotalDue <> 0)
            Select tji

LastUpdatedDate is a Nullable(Of Date) and TotalDue is a Nullable(Of Double) and when it translates to SQL it will work just fine.. The problems come about when the context switches from Linq-To-Entities to Linq-To-Objects (I have to watch closely for a .ToArray() or .ToList() or anything else that executes the SQL and fetches results) and much of the aggregation is doing exactly that.

I don't even know how many of these problems there are... Anyways, thanks for letting me rant a bit. Any suggestions? Is suicide a viable alternative to fixing thousands of lines of VB Linq statements of this nature?

Re: When to check for null ?

Brisingr Aerowing10-Jul-12 13:55

Brisingr Aerowing

10-Jul-12 13:55

And to those who will start the VB vs. C# debate, it is not VB that it the problem.

The Problem exists between the chair and the keyboard. (AKA, PEBKAC)

Bill Gates is a very rich man today... and do you want to know why? The answer is one word: versions.
Dave Barry
Read more at BrainyQuote[^]

Re: When to check for null ?

Sasha Laurel10-Jul-12 18:59

Sasha Laurel

10-Jul-12 18:59

PEBKAC ! I had forgotten about that one, thanks for reminding me. Yes, I would say that is usually a problem with computers. Also, thanks for reading my rant it makes me feel like at least someone listened.

As for the C# vs. VB debates, I don't really mind. It really gets so funny at times (people will argue the silliest things based on ideas that aren't even true). There is no problem with VB.Net especially for those of us who code using 'Option Strict On' (I'm really biased towards static typing). I find VB preferable because of the way you can easily tell where each block begins and ends. Doing so with this is hard for me: {{{{{{{{{{}}}}}}}}}} Confused | :confused:

Re: When to check for null ?

Brisingr Aerowing11-Jul-12 4:37

Brisingr Aerowing

11-Jul-12 4:37

I always have Option Strict On, and a lot of VB projects I download from different sites won't compile with it on, so I modify them to compile.

Bill Gates is a very rich man today... and do you want to know why? The answer is one word: versions.
Dave Barry
Read more at BrainyQuote[^]

Re: When to check for null ?

CDP180210-Jul-12 22:25

CDP1802

10-Jul-12 22:25

You are wrong. VB is the problem. It was always sold as sooooo easy to learn and use and then remained the safe harbor for all the horror coders they had picked up. A kind of Jurassic Park for coders.

Give a user a program and he can work for a day. Show the user how to program and you have work for the rest of your life.

At least artificial intelligence already is superior to natural stupidity

Re: When to check for null ?

Julien Villers10-Jul-12 23:24

Julien Villers

10-Jul-12 23:24

CDP1802 wrote:
Give a user a program and he can work for a day. Show the user how to program and you have work for the rest of your life.

Love that one Smile | :)

'As programmers go, I'm fairly social. Which still means I'm a borderline sociopath by normal standards.' Jeff Atwood

'I'm French! Why do you think I've got this outrrrrageous accent?' Monty Python and the Holy Grail

Re: When to check for null ?

CDP180211-Jul-12 0:26

CDP1802

11-Jul-12 0:26

But please don't get me wrong. I have met people who taught themselves and also were very capable of logical thinking. Once a mailman (who only had some experience on a C64) told me about the bugs in a Win32 program, where to look in the code and how to fix them. And then there is that great mass of users who think they know everything...

At least artificial intelligence already is superior to natural stupidity

Re: When to check for null ?

Julien Villers11-Jul-12 0:47

Julien Villers

11-Jul-12 0:47

Yup, I've got some mixed experiences too: on the one hand self-taught guy who did great complicated stuff, like expert systems, AI, etc... and on the other hand supposedly 'educated' engineers who couldn't tell a variable from an instruction.

And users, ah users, ...

Re: When to check for null ?

Sasha Laurel11-Jul-12 4:52

Sasha Laurel

11-Jul-12 4:52

Quote:
You are wrong. VB is the problem.

I'm afraid not. Go ahead and open your mouth and insert your shoe. In this case it is a combination of ignorance and arrogance that is the problem (something that you also seem to be displaying by this comment). Since you know so much about the culture of my workplace, why don't you expound upon that?

Re: When to check for null ?

CDP180212-Jul-12 1:28

CDP1802

12-Jul-12 1:28

Sasha Laurel wrote:
Go ahead and open your mouth and insert your shoe

No, thank you. But I would like to test Newton's third law. How would you like it? Shall I take that shoe and let it collide with your rear side or shall I just keep writing?

What I did say was, that VB was always advertised as 'easy to learn' and 'ideal for beginners'. If I may translate that: If you are too lazy to learn or too dumb for anything else, then use VB. What I did not say was that all people who use VB are of that kind.

Anyway, what you call arrogance is what I call self defense. I do not think that I can stop an army of the lazy and the dumb from doing vtheir evil, and therefore I do not preach. What I do is to stay away from them and not allow them to drag me into their desasters. It's remarkable how often things turned ugly when I did not manage to avoid VB.

Indeed I just have to look into source control, my bosses' chamber of horrors, to find 10+ years of madness. It was of course the bosses' pointy-hairedness that got them into this in the first place. It took some years for them to realize that it might help if they hire developers that actually learned their job. It's amazing how many of those failures were written in VB, or perfectly good projects converted to VB and then mercilessly butchered. Or former VB 'developers' who had turned 'professional' and looked for new places to commit the same old sins. The only projects that showed a better quality were programmed in C++, which does not have the reputation to be 'easy to learn' or 'ideal for beginners'. What a coincidence.

Sasha Laurel wrote:
Since you know so much about the culture of my workplace, why don't you expound
upon that?

For your sake, I hope that this is the culture of your workspace. For my part, I prefer to be called ignorant and arrogant before voluntarily getting myself into such a mess. At least that helped me to get together a longer list of projects that did not fail, which I can arrogantly point to. The irony is , that VB has evolved to a point where it had become usable, but less so its greatest users and supporters.

At least artificial intelligence already is superior to natural stupidity

Re: When to check for null ?

Sasha Laurel12-Jul-12 4:55

Sasha Laurel

12-Jul-12 4:55

Quote:
Shall I take that shoe and let it collide with your rear side

The "foot in mouth" thing is figurative, but since you seem to be speaking literally, I would hate to deprive you of the opportunity. I live in Salt Lake City, UT near Liberty Park. We can meet at that park at any time of your choosing where we can test Newton's third law to your hearts content.

As long as we can agree that you've conceded your point that "VB is the problem" by saying that the real problem is the devs who never get past it, then I don't think we are in disagreement at all. As a side note, you can attack VB6 and prior all you want, I don't even care if you make silly blanket statements about those versions.

Quote:
For your sake, I hope that this is the culture of your workspace.

In my defense, I didn't really have any better options at the time. I would prefer to work in an environment that "separates the men from the boys" so to speak, but for now, that will just have to wait. Even still, we have plenty of very successful products, all written in some form of VB (mostly .Net).

You come across as very angry and mean, so if I offended you please accept my apologies. I'll rescind my comment about you being ignorant and arrogant, as long as it is fully obvious that you are neither.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.