Nested If statements

  • Hello,
    I have a prob with the follow  stored procedure, can any one help me with this please
    I don't know exactly when i must use begin... End, and when not
    here is my code

    USE [Eurosort]
    GO
    /****** Object: StoredProcedure [dbo].[U_SaveScanregistraties]  Script Date: 12/11/2017 12:55:08 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:          <Benny>
    -- Create date: <12/11/2017,,>
    -- Description: <Save scanregistration,>
    -- =============================================
    ALTER PROCEDURE [dbo].[U_SaveScanregistraties] @id as int,
    @scantijd as time(0) ,
    @returnValue as bigint out     
    AS

    BEGIN TRY

    Begin
    SET NOCOUNT ON;
    DECLARE @Werkdagdatum as date = (Select Top 1 datum from Werkdag where Werkdag.idpersoneel = @id order by datum desc )
    DECLARE @scanin as time(0) = (Select Top 1 scanin from Werkdag where Werkdag.idpersoneel = @id order by datum desc )
    DECLARE @scanuit as time(0) = (Select Top 1 scanuit from Werkdag where Werkdag.idpersoneel = @id order by datum desc )
    DECLARE @datum as date = GETDATE()
    DECLARE @naam nvarchar(30) = (Select naam from Personeel where Personeel.id = @id )
    DECLARE @voornaam nvarchar(30) = (Select voornaam from Personeel where Personeel.id = @id )
    DECLARE @rsznummer nvarchar(15) = (Select rsznummer from Personeel where Personeel.id = @id )
    DECLARE @uurloon numeric(4,2) = (Select uurloon from Personeel where Personeel.id = @id )
    IF @naam IS NOT NULL -- geldig id
    Begin
     IF CONVERT(DATE,@Werkdagdatum) = CONVERT(DATE, @datum) --DAgvergelijk
     -- Begin --1
      IF @scanin IS NULL -- record bestaat maar scanin is leeg
      --BEGIN --2
       UPDATE Werkdag
       SET scanin = @scantijd
      -- END --2
      ELSE
      BEGIN--2
       DECLARE @scaninlast as time(0) -- = DATEADD(minute,3,@scanin) -- er moet minstens 3 minuten verlopen zijn
       -- @scaninlast = @scantijd - @scanin
       If @scantijd - @scanin > 3 -- als groter kijken of scanuit infevuld is
         Begin --3
       If @scanuit IS NULL -- als scanuit leeg
      -- BEGIN --4
        UPDATE Werkdag
        SET scanuit = @scantijd
       -- END --4
       ELSE -- scanuit niet leeg
        Begin --4
        DECLARE @scanuitlast as time(0) = DATEADD(minute,3,@scanuit) -- er moet minstens 3 minuten verlopen zijn
        If @scanuitlast - @scantijd > 3 -- als groter nieuw record aanmaken
        -- BEGIN --5
         INSERT INTO Werkdag
         (
         idpersoneel ,
         rsznummer ,
         datum ,
         uurloon
         )
         values
         (
         @id,     
         @rsznummer ,
         @datum,
         @uurloon
         )
        END --einde nieuw record 5
        End --scanuit niet leeg 4
            End --scanin ingevuld
            END -- 2
      ELSE -- Deze dag bestaat nog niet een nieuw record bijmaken
       BEGIN --4
       INSERT INTO Werkdag
       (
       idpersoneel ,
       rsznummer ,
       datum ,
       uurloon
       )
       values
       (
       @id,
       @rsznummer ,
       @datum,
       @uurloon
       )
      -- END
     -- END -- einde dag vergelijken

    ---------------------------------------------------------
    -----------Af tot hier
    --------------------------------------------------------------

    Ken er nog niet veel van, maar leer het wel
    Do not know much about it yet, but learn

  • benny 52509 - Saturday, November 18, 2017 1:24 AM

    USE [Eurosort]
    GO
    /****** Object: StoredProcedure [dbo].[U_SaveScanregistraties]  Script Date: 12/11/2017 12:55:08 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:          <Benny>
    -- Create date: <12/11/2017,,>
    -- Description: <Save scanregistration,>
    -- =============================================
    ALTER PROCEDURE [dbo].[U_SaveScanregistraties] @id as int,
    @scantijd as time(0) ,
    @returnValue as bigint out     
    AS

    BEGIN TRY

    Begin
    SET NOCOUNT ON;
    DECLARE @Werkdagdatum as date = (Select Top 1 datum from Werkdag where Werkdag.idpersoneel = @id order by datum desc )
    DECLARE @scanin as time(0) = (Select Top 1 scanin from Werkdag where Werkdag.idpersoneel = @id order by datum desc )
    DECLARE @scanuit as time(0) = (Select Top 1 scanuit from Werkdag where Werkdag.idpersoneel = @id order by datum desc )
    DECLARE @datum as date = GETDATE()
    DECLARE @naam nvarchar(30) = (Select naam from Personeel where Personeel.id = @id )
    DECLARE @voornaam nvarchar(30) = (Select voornaam from Personeel where Personeel.id = @id )
    DECLARE @rsznummer nvarchar(15) = (Select rsznummer from Personeel where Personeel.id = @id )
    DECLARE @uurloon numeric(4,2) = (Select uurloon from Personeel where Personeel.id = @id )
    IF @naam IS NOT NULL -- geldig id
    Begin
     IF CONVERT(DATE,@Werkdagdatum) = CONVERT(DATE, @datum) --DAgvergelijk
     -- Begin --1
      IF @scanin IS NULL -- record bestaat maar scanin is leeg
      --BEGIN --2
       UPDATE Werkdag
       SET scanin = @scantijd
      -- END --2
      ELSE
      BEGIN--2
       DECLARE @scaninlast as time(0) -- = DATEADD(minute,3,@scanin) -- er moet minstens 3 minuten verlopen zijn
       -- @scaninlast = @scantijd - @scanin
       If @scantijd - @scanin > 3 -- als groter kijken of scanuit infevuld is
         Begin --3
       If @scanuit IS NULL -- als scanuit leeg
      -- BEGIN --4
        UPDATE Werkdag
        SET scanuit = @scantijd
       -- END --4
       ELSE -- scanuit niet leeg
        Begin --4
        DECLARE @scanuitlast as time(0) = DATEADD(minute,3,@scanuit) -- er moet minstens 3 minuten verlopen zijn
        If @scanuitlast - @scantijd > 3 -- als groter nieuw record aanmaken
        -- BEGIN --5
         INSERT INTO Werkdag
         (
         idpersoneel ,
         rsznummer ,
         datum ,
         uurloon
         )
         values
         (
         @id,     
         @rsznummer ,
         @datum,
         @uurloon
         )
        END --einde nieuw record 5
        End --scanuit niet leeg 4
            End --scanin ingevuld
            END -- 2
      ELSE -- Deze dag bestaat nog niet een nieuw record bijmaken
       BEGIN --4
       INSERT INTO Werkdag
       (
       idpersoneel ,
       rsznummer ,
       datum ,
       uurloon
       )
       values
       (
       @id,
       @rsznummer ,
       @datum,
       @uurloon
       )
      -- END
     -- END -- einde dag vergelijken

    ---------------------------------------------------------
    -----------Af tot hier
    --------------------------------------------------------------

    Formatted
    😎

  • Can you describe what you are trying to do? What is the logic?
    😎

    At the first glance, it looks like there is quite an opportunity for improvements within that code.

  • Thx mr Eriksson for your quickley reaction
    15 years ago i create 1 app in Delphi with foxpro as database
    now i wil create this in VB.net and Sql Server
    the prog is for employment registration with barcode
    by scanning
    the program must look or there is already a record with this date and Id,
    Employe can leave company and comeback later on the day

    now i explain the follow steps by registry
    1] employe  146 scan at 16/11/2017 06:33:15
    2] searching record with id 146 and date 16/11/2017
     if  found look to scanin
       if scanin IS NULL
         scanin = registrytime
       Else
        compare scanin  and registrytime
         if different between registry time and scanin > 3 minutes ---( security agains double registry)
           look at scanout
          If scanout IS NULL 
           scanout = registrytime
         Else
         compare scanout and registrytime
          if different between registry time and scanout > 5 minutes ---( security agains double registry)
          insert new record
    Else -- record not found
       insert new record
    -----------------------
    The next step = calculate the working hours
    I hope you understand the logic.
    Thx almost mr Eriksson

    Ken er nog niet veel van, maar leer het wel
    Do not know much about it yet, but learn

  • You are welcome!
    Can you post the DDL (Create Table) scripts, some sample data as an insert statement and the expected results please? That is by any means the best way of getting answers around here 😉
    I'm too busy at the moment to look into this but if you post the samples then someone will pick this up!
    😎

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply