How to return a statements in stored procedure

Question

Hi

This is my procedure :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspChangeMemberRole]

@TeamID INT,

@MemberID INT,

@MemberRole TINYINT
AS BEGIN

SET NOCOUNT ON;

UPDATE [dbo].[TeamMember] SET MemberRole=@MemberRole WHERE TeamID=@TeamID AND MemberID=@MemberID;
return @MemberRole;

End

When I Execute this procedure i want to return MemberRole Successfully Changed.

Please give me a solution how to return

Sivakumar · Answer

Yes manas this procedure is correct but i want to display member role is successfully changed statement when i execute procedure.

Manas Mohapatra · Answer

ALTER PROCEDURE [dbo].[uspChangeMemberRole] @TeamID INT @MemberID INT @MemberRole TINYINT OUTPUT AS BEGIN SET NOCOUNT ON; UPDATE [dbo].[TeamMember] SET MemberRole=@MemberRole WHERE TeamID=@TeamID AND MemberID=@MemberID; SELECT @MemberRole = MemberRole FROM [dbo].[TeamMember] WHERE TeamID=@TeamID AND MemberID=@MemberID; End Secondly if you are sending MemberRole from code to StoredProcedure why do you need to send it again from StoredProcedure to code.