locked
Asp.net web SQL Stored Procedure Stop adding duplicate values RRS feed

  • 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 

    Monday, December 25, 2017 2:06 AM

Answers

  • User-1716253493 posted

    try like this

    IF @Action = 'INSERT' AND NOT EXISTS(SELECT * From Appt WHERE Appt=@appt)
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 5, 2018 4:03 AM

All replies

  • User-1716253493 posted

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

    Monday, December 25, 2017 2:36 AM
  • 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

    Tuesday, December 26, 2017 9:34 AM
  • 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

    Friday, January 5, 2018 3:34 AM
  • User-1716253493 posted

    try like this

    IF @Action = 'INSERT' AND NOT EXISTS(SELECT * From Appt WHERE Appt=@appt)
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 5, 2018 4:03 AM