IF EXIST - DO NOTHING

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 6, 2011 at 4:39 pm

  #1391528

  If you're going to rollback, you need a begin transaction, however you don't need a rollback, at the point you have it there's nothing to rollback

  You also don't need that second check.

  IF EXISTS (...)

  RAISERROR (...);

  RETURN;

  ELSE

  INSERT ...

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Performance Guard (Shehap)

  Ten Centuries

  Points: 1297

  More actions

  October 9, 2011 at 3:45 pm

  #1392226

  I do believe much from the business logics shown above that the query below with AND gates is the correct one

  if not exists (select 1 from customer where col_1 = X and col_2 = Y and col_3 = Z)

  begin

  end

  [font="Arial Black"]Performance Guard[/font]

  [font="Courier New"]Just ask me to get 0 sec for all queries[/font]

• darkbytegirl

  Grasshopper

  Points: 24

  More actions

  October 10, 2011 at 12:50 am

  #1392272

  i agree. i did the test below:

  it is your logic underneath the condition that changes based on exist and not exist, not your "and" and "or" gates:

  declare @temp table

  (

  a varchar(50),

  b varchar(50),

  c varchar(50)

  )

  insert into @temp values('a','b','c')

  if not exists (select 1 from @temp where a = 'a' AND b = 'b' AND c = 'c')

  print 'do something'

  else print 'do nothing'

  if exists (select 1 from @temp where a = 'a' AND b = 'b' AND c = 'c')

  print 'do nothing'

  else print 'do something'