I am trying to use the following stored procedure but there are some instances where only the incremental happens and the code does not run. What I need is that, when the program enters the if statement, either it should run both the statements or None.
Stored procedure goes like this:
USE [SSWYPE_WEBDB]
GO
/****** Object: StoredProcedure [dbo].[spflpunch] Script Date: 11/20/2017 6:27:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spflpunch]
AS
BEGIN
declare @id numeric(18,0)
declare @studname nvarchar(50)
declare @punchtime datetime
declare @samedaycount numeric(2)
select @id = (max(lastid)) from [smartswype].[dbo].[read]
print @id
select @studname = studname from [SSWYPE_WEBDB].[dbo].[attdview] where id=@id
print @studname
select @punchtime = punchtime from [SSWYPE_WEBDB].[dbo].[attdview] where id = @id
print @punchtime
--select @punchvarchar= CONVERT(VARCHAR(10),@punchtime, 103) + ' ' + CONVERT(VARCHAR(5),
-- @punchtime, 14)
if @id = (select max(id) from [SSWYPE_WEBDB].[dbo].[attdview])
BEGIN
select @samedaycount = count(*) from [SSWYPE_WEBDB].[dbo].[attdview] where (studname=@studname and CONVERT(date, punchtime) = CONVERT(date, @punchtime)) -- If firstpunch = 1 then it is the first punch
print @samedaycount
if @samedaycount =1
BEGIN
insert into [smartswype].[dbo].[firstlastpunch] ([studname]
,[date]
,[punch1]
,[punch2])
values(@studname, CONVERT(date, @punchtime), @punchtime,null);
update [smartswype].[dbo].[read] set lastid=@id+1;
END
ELSE IF (@samedaycount > 1)
BEGIN
update [smartswype].[dbo].[firstlastpunch]
set punch2 = @punchtime where (studname = @studname and date = CONVERT(date, @punchtime));
update [smartswype].[dbo].[read] set lastid=@id+1;
END
END
END
Aucun commentaire:
Enregistrer un commentaire