Update in Stored procedure not working

Transact-SQL
1

Hi,

In the below SP

Alter procedure dbo.update_transaction
AS
SET NOCOUNT ON;

DECLARE @cnt AS INT

SET @cnt = (select cID from table3) ----value is 1 or 2

If @cnt = 1
Begin

Update statement in table1

Insert Statement in table2

End
Else If @cnt = 2
Begin

Update statement on table1

Insert Statement in table2

End

when Stored procedure ran it executed successfully with no error, Insert statement is working fine, but Update is not working. When Update statement is executed separately it is working fine.

Is Begin/Commit Transaction is required or missing something else. Using system service account. So no issue on user side.

thanks in advance.

2

You should always have a Begin/commit to control the transaction. Also Try/Catch. As for the update not working, you've given very little as to what it is doing. The service account shouldn't have anything to do with it or you wouldn't be able to insert. Are you using dynamic sql to insert/update? You can comment out the Set NoCount On to see how many rows are being affected with each statement, at lease while testing. Can you provide more detail on what the Update/Insert statements are doing? Are there where clauses being used?

1 Like
3

Are you sure the value of the cID column in the table will always be 1 or 2?

Is it possible the code needs to be?
SET @cnt = (SELECT COUNT(*) FROM dbo.table3)

4

thanks for the replies.

No, the query is not dynamic sql. there is where clause in Update and no filter in Insert. I tested the query in DEV by commenting No Count on. there was around 121 records updated, so I think some data issue in PROD. Also enabled profiler for the stored procedure, it showing 121 record count. As safety added Begin transaction/Commit. Planning to enable Profiler in PROD to check the record count.