Asp.net web SQL Stored Procedure Stop adding duplicate values

Question

User810354248 posted

in my SQL stored procedure i am using this SP to add, edit, delete in asp.net web with VB code.

USE [BaijuEP]
GO
/****** Object:  StoredProcedure [dbo].[Appt_CRUD]    Script Date: 12/25/2017 07:33:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Appt_CRUD]
      @Action VARCHAR(10)
      ,@appid INT = NULL
      ,@Appt VARCHAR(254) = NULL
AS
BEGIN
      SET NOCOUNT ON;
 
      --SELECT
    IF @Action = 'SELECT'
      BEGIN
            SELECT appid,Appt
            FROM Appt ORDER by Appt desc
      END
 
      --INSERT
    IF @Action = 'INSERT'
      BEGIN
            INSERT INTO Appt(Appt)
            VALUES (@Appt)
      END
 
      --UPDATE
    IF @Action = 'UPDATE'
      BEGIN
            UPDATE Appt
            SET Appt = @Appt
            WHERE appid = @appid
      END
 
      --DELETE
    IF @Action = 'DELETE'
      BEGIN
            DELETE FROM Appt
            WHERE appid = @appid
      END
END

The above SP works fine. But it also allows adding duplicate data (eg Baiju is an appt added again if i will add it accepts). Iwant to stop adding duplicate vaues 

Answer 1

User-1716253493 posted

you can check using IF NOT EXISTS (SELECT * FROM THETABLE WHERE condiion) to insert

Answer 2

User-707554951 posted

Hi Baiju EP,

1. As oned_gk suggestion, you could to check first:

Related thread for reference:

https://forums.asp.net/t/1850573.aspx?Stored+Procedure+to+prevent+duplicate+entry

2. you could try to Create a table type to match your input parameters

Then change your stored procedure to accept such a table type parameter

Sample  code as below:

-- Create a table type to match your input parameters
CREATE TYPE IdNameTable AS TABLE 
( ID INT, Name NVARCHAR(50) );
GO

-- change your stored procedure to accept such a table type parameter
ALTER PROCEDURE [dbo].[Register]
    @Values IdNameTable READONLY
AS
BEGIN
    BEGIN TRY

        INSERT INTO dbo.Group (Id, Name) 
        SELECT 
           Id, Name
        FROM
           @Values
        WHERE 
           ID NOT IN (select ID from Group)

        SELECT 0
    END TRY
    BEGIN CATCH
        SELECT -1
    END CATCH
END

Related thread:

https://stackoverflow.com/a/25134323

3. Suppose 'username' is your primary key and therefore unique. Then you can check whether it already exists in the database or not as follows:

4. Or use unique constraint to ensure teh value in a column are different

https://www.w3schools.com/sql/sql_unique.asp

Best regards 

Cathy

Answer 3

User810354248 posted

I was not able to proceed as i am new in stored procedures. If the below code is modified to stop duplicate entry of APPT please.

IF @Action = 'INSERT'
      BEGIN
            INSERT INTO Appt(Appt)
            VALUES (@Appt)
      END

Answer 4

User-1716253493 posted

try like this

IF @Action = 'INSERT' AND NOT EXISTS(SELECT * From Appt WHERE Appt=@appt)