"after several updates and inserts..."

Maybe it's worth asking... does your procedure specify 'set xact_abort on'?  When a proc has multiple DML statement in a transaction and without xact_abort setting can...

Hi I posted this answer before but it appears to be marked as SPAM.  Not sure why.  This site has an annoying text editor.

What this code is doing.

1. Declares 3...

• This reply was modified 5 years, 9 months ago by  Steve Collins.

Yes exactly.  Something like:

drop proc if exists wm.api_comment_post;

go

create proc wm.api_comment_post

@securitystamp uniqueidentifier,

@cw_id bigint,

@comment nvarchar(1024),

@test_id bigint output,

@response nvarchar(max) output

as

set nocount on;

set xact_abort on;

begin transaction

begin try

declare

@awc_id bigint;

insert comments(u_id, w_id, cw_id, comment, created_dt, edited_dt)

select

wm.func_user_id(@securitystamp),

cw.w_id,

cw.cw_id,

@comment,

sysutcdatetime(),

sysutcdatetime()

from

wm.class_workouts...

Reasons to use output parameters in stored procedures:

1. To return db keys generated within the procedure
2. To return data (version id's, timestamp's, etc) which changed/updated as a result of actions...