Execute Stored Procedure and get the return value

[ssreevidya.m]

Hai,

I Have an sp like this:

ALTER procedure [dbo].[SP_ClassInsertion]
@ClassType varchar(50),
@ClassTypeId varchar(50),
@ClassName varchar(50),
@SchoolId varchar(50),
@ClassID  varchar(50),
@BatchId  varchar(50),
@Userid uniqueidentifier,
@ClassCategoryId varchar(50),
@SchoolClassCategoryId varchar(50)

as

declare @STATUS  int

begin
if exists(select Class_Id from SCH_Class where Class_Name=@ClassName  and School_Id=@SchoolId and Is_Valid=1)
begin
set @STATUS=0
end

else
begin


insert into  SCH_Class  (Class_Id,Class_Type_Id,Batch_Id,School_Id,Class_Name,Created_Date,Updated_Date,Created_By,Updated_By,Is_Valid) values (@ClassID,@ClassTypeId,@BatchId,@SchoolId,@ClassName,GetDate(),GetDate(),@Userid,@Userid,1)


set @STATUS=1

return @STATUS
end

Can u please help me to execute this sp and get the return value from it.

Regards,
sreevidya

[pritaeas]

DECLARE @RET int

DECLARE @ClassType varchar(50)
DECLARE @ClassTypeId varchar(50)
DECLARE @ClassName varchar(50)
DECLARE @SchoolId varchar(50)
DECLARE @ClassID  varchar(50)
DECLARE @BatchId  varchar(50)
DECLARE @Userid uniqueidentifier
DECLARE @ClassCategoryId varchar(50)
DECLARE @SchoolClassCategoryId varchar(50)

-- TODO: Set parameter values here.

EXECUTE @RET = SP_ClassInsertion
  @ClassType,
  @ClassTypeId,
  @ClassName,
  @SchoolId,
  @ClassID,
  @BatchId,
  @Userid,
  @ClassCategoryId,
  @SchoolClassCategoryId

GO

[ssreevidya.m]

Hai ,
Thank you for your replay.
I execute the sp successfully , But how can i get the @RET as output.

[pritaeas]

add OUTPUT I think.

[ssreevidya.m]

Hai ,
Thanks for yuor replay,
But in which place i add the OUTPUT.

[pritaeas]

See the documentation for an example.

[adam_k]

change this part:

ALTER procedure [dbo].[SP_ClassInsertion]
@ClassType varchar(50),
@ClassTypeId varchar(50),
@ClassName varchar(50),
@SchoolId varchar(50),
@ClassID  varchar(50),
@BatchId  varchar(50),
@Userid uniqueidentifier,
@ClassCategoryId varchar(50),
@SchoolClassCategoryId varchar(50)

as

declare @STATUS  int

begin...

to

ALTER procedure [dbo].[SP_ClassInsertion]
@ClassType varchar(50),
@ClassTypeId varchar(50),
@ClassName varchar(50),
@SchoolId varchar(50),
@ClassID  varchar(50),
@BatchId  varchar(50),
@Userid uniqueidentifier,
@ClassCategoryId varchar(50),
@SchoolClassCategoryId varchar(50),
@STATUS  int OUTPUT
as 
begin...

[ssreevidya.m]

Hai ,
Thanks for your reply and i got it.