Get a return value from a StoredProcedure

[thesti]

hi,

is there anyway to know whether a INSERT/UPDATE/DELETE stored procedured was executed successfully?

for example, if i have the following StoredProcedure :

create procedure sp_add_employee
@userid int,
@password varchar(20)
as
insert into employee values(@userid,@password)

can i get a return value from the stored procedure to know whether the insert is success/failure?

Thank you.

[deepuv04]

hi,

use exception handling (try catch blocks) and return some value which will convey the result of the sp.

for example
create procedure sp_add_employee
@userid int,
@password varchar(20)
@Result INT OUTPUT
as
BEGIN
BEGIN TRY
insert into employee values(@userid, @password)
SET @Result = 1
END TRY
BEGIN CATCH
SET @Result = 0
END CATCH

SELECT @Result
END

on executing this if you get the result as 1 means success if 0 failure.


thanks.