CODEWITHSUNDEEP
c#.nett-sqlstored-proceduresinsert
Bolu
Bolu

Reputation: 8786

Sql Server run update stored procedure after insert stored procedure

I have two stored procedures one is to insert a record to the table, the other is to update one column of this record.

The insert one runs first, then I will run some other processes (including stored procedures used for other tables.) and finally I will run the update stored procedure to update the record as completed.

However, sometimes the update one cannot find the match (the one just inserted, I use some variables (one of them is in type datetime) that I passed to the insert stored procedure to find the record just inserted then to update. I have used a update trigger to find this reason.)

How can I make sure the data is already in the table before I run the update? do I need a while loop here?

EDIT: I'm running these all in one BackgroundWorker.DoWork Eventhandler

Upvotes: 2

Views: 769

Answers (2)

Kevin Nelson
Kevin Nelson

Reputation: 7663

This isn't asynchronous from the sounds of it, so you shouldn't need to have your update script wait for the insert.

Maybe I'm misunderstanding. However, what is sticking out to me is that you are saying that you are finding the row to update with datetime rather than its identity. If you have your insert proc return SCOPE_IDENTITY, then pass that to the update proc...it sounds like that should fix your problem where the update cannot find the right row.

Upvotes: 3

Petr Behenský
Petr Behenský

Reputation: 620

Run all DB stuff in one transaction.

Simple Example:

using(TransactionScope scope = new TransactionScope())
{
  // init connection, command etc.

  ...

  insertCommand.ExecuteNonQuery();

  ...

  updateCommand.ExecuteNonQuery();

  scope.Complete();
}

Upvotes: 0

Related Questions