Hi there, I have very strange problem with MSSQL 2k stored procedure.
The stored procedure takes 3 parameters, the body has two parts: record
deletion and record insertion.
The deletion attempt is being made every time, no matter if the table is
empty or not.
Then the insertion happens. The problem is that when I call it 2 times
inside a transaction, I am not getting 2 records in the table.
Here the pseudo-code that represents what I am doing  - I execute it from my
application using ADO 2.5 and Delphi5:
MyConnection.BeginTrans;
     myStoredProc( 1, 100,  1000   )
     myStoredProc( 2,  200,  2000  )
MyConnection.EndTrans;

I do not understand why, but after executing this code I [usually] have only
record in my table (2,200,2000)
Here is the full body of the stored procedure in question:

CREATE PROCEDURE dbo.spModuleChange  @ID  smallint, @Module  integer, @Key
integer
as begin
 delete from pModule where (Module = @Module) and (Key=@Key)
  insert into pModule( ID, Module, Key  )
  values (@ID, @Module, @Key )
end

When I modify the stored procedure that it executes 'delete' part only if
any record exists, the problem goes away.
I think it must be something about that 'delete' line - an exception that I
do not handle or something similar...
Does anybody have a clue

Thank you very much,
Marek