sql - TSQL - Return value from sp when sp does not contain return -
i have stored procedure called stored procedure
alter procedure [dbo].[usp_test] begin declare @errorcode int declare @lastidentity int select @errorcode = @@error if @errorcode=0 begin update vehicle set model='1996----------' make='merc' select @errorcode = @@error select @lastidentity = @@identity end print 'usp_test lastidentity=' + convert(varchar(10), isnull(@lastidentity,0)) print 'usp_test errorcode=' + convert(varchar(10), @errorcode) end
if call stored procedure this
declare @retval int exec @retval=usp_test print 'return value ' + convert(varchar(10), @retval)
i following messages
msg 8152, level 16, state 14, procedure usp_test, line 14 string or binary data truncated. statement has been terminated. usp_test lastidentity=0 usp_test errorcode=8152 return value -6
by adding return 0 @ end , return @errorcode after select @errorcode... have nice clean way of returning error , subsequently handle it. surprise without return's return value of -6. can explain why case?
copied this answer
if have return statement explicit return value, of course return value.
but if there no return statement, error occurs during execution, return value 10 minus severity level of error. division 0 level 16, return value -6. permissions errors typical level 14, return value -4.
as may guess not terribly useful, this: 0 success, , else error.
Comments
Post a Comment