Check data exists at specific interval before insert in SQL Server

smile
 
on Jun 26, 2021 05:39 AM
347 Views

I want to add data in the table tblDoctorCall

CallID, D_Name, P_Name, Service, Desk, Ticket

Keypoints:

1- Database is online

2- there are 30 pc and each pc has installed setup and has its own login.

3- There is 95% chances that many doctor call the patient at same time

4- when patient is called then the data will be inserted in the following table

tblDoctorCall

CallID, D_Name, P_Name,Service,Desk,Ticket

5- I want to restrict insert data at same time. so i want to set inverval of 10 second to insert record by each login in each pc.

6- for this purpose i wrote the following Store Procedure

USE [Shop]
GO
/****** Object:  StoredProcedure [dbo].[sp_DocCallAdd]    Script Date: 25-Jun-21 5:41:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_ICall]
@CDate  varchar(50),
@DName	nvarchar (50) , 
@Desk	nvarchar (15) , 
@Service	nvarchar (50) , 
@PName	nvarchar (50) , 
@Ticket	 nvarchar (50) ,  
@ETime  varchar(50),
@OID int output
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
               
SET NOCOUNT ON;

---Checking Condition if User exists or not if user not exists returns different message if exists returns different message

IF NOT EXISTS
	(
	SELECT ETime  FROM tblDoctorCalls 
	WHERE CONVERT(DATETIME,ETime) >= CONVERT(DATETIME,DATEADD(SECOND,-4,@ETime)) and P_Name=@PName	     
    )
BEGIN
INSERT INTO tblDoctorCalls ([C_Date],[D_Name],[Desk],[Service],[P_Name],[Ticket],[ETime])
VALUES
(@CDate,@DName,@Desk,@Service,@PName,@Ticket,@ETime)

--If User Successfully Registerd I am returing this Message as Output Parameter
SET @OID=SCOPE_IDENTITY()
END
END

it seems to me it is not making interval. i checked the database.

how to fix it?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 26, 2021 05:39 AM
smile says:
SELECT ETime  FROM tblDoctorCalls
    WHERE CONVERT(DATETIME,ETime) >= CONVERT(DATETIME,DATEADD(SECOND,-4,@ETime)) and P_Name=@PName  

Change with below code.

SELECT ETime  FROM tblDoctorCalls
WHERE CONVERT(DATETIME,ETime) >= CONVERT(DATETIME,DATEADD(SECOND,-10,@ETime)) and P_Name=@PName