lundi 20 novembre 2017

SQL Server if statement does not execute as expected

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