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.