CODEWITHSUNDEEP
sql-serverstored-procedures
Thiyagarajan S
Thiyagarajan S

Reputation: 5

Check if a given ID exists using Stored Procedure by If Exists or If Not Exists?

I am using stored procedures for insert, update, delete operations in database. To check if a given ID exists, I use if exists condition in my code. Here's my issue: I am given the ID which is in database, but the condition checks and allows duplicate values to be stored in database.

My stored procedure code:

SET NOCOUNT ON;

If @Action = 'Insert'   --used to Insert records
Begin
    If Exists(select BatchNo from Work_In_Progress where BatchNo = '@BatchNo')
    Begin
        set @Error='Already Exists'     
    End     
    Else 
    Begin                  
        insert into Work_In_Progress 
        values (@BatchNo, @ISR_No, @CatalogNo, @Brandname, @Productname, @Operation_Code, @Operation_Name, @Machine_Id, @Received_Qty, @Finished_Qty, @Balance_Qty, @Accepted_Qty, @Rejected_Qty, @Startdate, @Starttime, @Enddate, @Endtime, @Accp_MTS_No, @Rej_MTS_No, @Remarks, @Work_Done_By, @Approved_By, @Updated_Date, @Updated_By, @InspectionStatus);

        insert into Sample_Inspection   
        values(@BatchNo,@Operation_Code,@Inspection_Name,@Insp_Parameter,@Insp_Tolerance,@Observation,@Sample_Rcd_Qty,@Sample_Accepted_Qty,@Sample_Rejected_Qty,@Sample_Rework_Qty,@Sample_Accp_MTS_No,@Sample_Rej_MTS_No,@Sample_Rework_MTS_No,@Sample_Remarks,@Sample_WorkDone_By,@Sample_WorkDone_Date,@Sample_Approved_By,@Sample_Updated_Date,@Sample_Updated_By);
   End
End

How can I overcome this issue?

Upvotes: 0

Views: 5073

Answers (2)

Dhaval
Dhaval

Reputation: 2861

Do it without the quotes around @BatchNo:

If Exists(select BatchNo from Work_In_Progress where BatchNo = @BatchNo)

Upvotes: 1

User125
User125

Reputation: 108

declare @id int =null,
@ret int=null
if Not exists(select id from table1 where id=@id)
begin
 --insert query,
     --set  @ret=@id
     end
      else 
   begin
    ---set  @ret=-1
    end

Upvotes: 0

Related Questions