November 18, 2017 at 1:24 am
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
November 18, 2017 at 2:03 am
benny 52509 - Saturday, November 18, 2017 1:24 AMUSE [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
ASBEGIN 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
😎
November 18, 2017 at 2:06 am
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.
November 18, 2017 at 2:44 am
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
November 18, 2017 at 9:01 am
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