How to Stop Inserting?

How would i will stop looping if the value is already existing?

set ANSI_NULLS ON    
set QUOTED_IDENTIFIER ON    
GO

ALTER PROCEDURE [dbo].[SD_Sproc_Result]     
-- Add the parameters for the stored procedure here
    @employeeid bigint,
    @providercode varchar(100)
AS
BEGIN

  IF NOT EXISTS(SELECT * 
                  FROM TR_employeesprovidercode    
                 WHERE employeeid = @employeeid
                  AND providercode = @providercode)
  BEGIN
      Insert into TR_employeesprovidercode 
        (employeeid, providercode) 
      values 
        (@employeeid, @providercode)
  END

END

C# code

s my code in C#...

foreach (ArrayList item in ArrData)

{

    HCSProvider.NewProviderResult oResult; oResult =
       oHCSProvider.CreateNewProvider(providercode, oProviderDetail);

    DBInterface ProviderDetail = new DBInterface();

    ProviderDetail.InsertProvider(
        Convert.ToInt64(providercode),
        Conver开发者_Go百科t.ToString(oProviderDetail));

}

---

The NOT EXISTS first doesn't scale well and you can still get duplicate errors.

Why not try something like this?

...
DECLARE @rtn int
BEGIN TRY
  Insert into TR_employeesprovidercode 
    (employeeid, providercode) 
  values 
    (@employeeid, @providercode)
  SET @rtn = 0
BEGIN TRY
BEGIN CATCH
  IF ERROR_NUMBER() <> 2627
     RAISERROR ('real error', 16, 1)
  SET @rtn = 1
END CATCH
RETURN @rtn

---

You could use and output parameter in your stored procedure and populate the value if the entry is a duplicate. Then test the parameter and break the loop if the value already exists.

Here's the MySql Tutorial on this.

---

Add an ELSE part to the stored proc and return some constant.

IF NOT EXISTS
 BEGIN   
   ...   
 END
ELSE
 RETURN 0;

In your loop check for the return value and break from the loop.

foreach(var item in myArray)
{
    SqlCommand cmd = new SqlCommand(); // command for stored proc
    ...
    var result = cmd.ExecuteScalar();

    if(result == 0)
       break;
}

---

You'd need to return a value from the stored procedure that you can check, and then break the loop on an appropriate return value.