SQL Server Stored Procedure Update and Return Single Value -

i need call stored procedure, give report id (int) , have update report table confirmation number (varchar) (confirmation # generated in stored procedure) , return confirmation number (i need return web service/website).

my stored procedure code:

declare procedure [dbo].[spupdateconfirmation] @reportid int begin    declare @confirmation varchar(30) = replace(replace(replace(convert(varchar(16),current_timestamp,120),'-',''),' ',''),':','')+convert(varchar(24),@reportid)    print @confirmation     update report    set  confirmation = @confirmation    reportid = @reportid;     return @confirmation end 

my call stored procedure:

execute [spupdateconfirmation] 2 

i confirmed in table value inserted error message:

2013050219072  (1 row(s) affected) 

msg 248, level 16, state 1, procedure spupdateconfirmation, line 12
conversion of varchar value '2013050219072' overflowed int column.
'spupdateconfirmation' procedure attempted return status of null, not allowed. status of 0 returned instead.

question: did wrong?

i understand overflow is, value large int, used convert varchar, inserted table column type varchar(30)

i tested statement in sql , works fine:

print replace(replace(replace(convert(varchar(16),current_timestamp,120),'-',''),' ',''),':','')+convert(varchar(24),2) 

it returns: 2013050219162

return stored procedure allows integer values. specifically, documentation states:

return [ integer_expression ]

if want return varchar value, can use output parameter

create procedure [dbo].[spupdateconfirmation] @reportid int, @confirmation varchar(30) output --begin set @confirmation = replace(replace(replace(convert(varchar(16),current_timestamp,120),'-',''),' ',''),':','')+convert(varchar(24),@reportid) --print @confirmation     update report     set  confirmation = @confirmation     reportid = @reportid;     --return @confirmation --end go 

to called this

declare @reportid int; -- set @reportid declare @confirmation varchar(30); exec [dbo].[spupdateconfirmation] @reportid, @confirmation output; -- @confirmation contains value set sp call above 

a simpler option if calling c# select output single-row, single-column result , use sqlcommand.executescalar, e.g.

create procedure [dbo].[spupdateconfirmation] @reportid int --begin declare @confirmation varchar(30); set @confirmation = replace(replace(replace(convert(varchar(16),current_timestamp,120),'-',''),' ',''),':','')+convert(varchar(24),@reportid) --print @confirmation     set nocount on; -- prevent rowcount messages     update report     set  confirmation = @confirmation     reportid = @reportid;     --return @confirmation     set nocount off; -- re-enable following select     select @confirmation;   -- value --end go