sql - How to get Return Value of a Stored Procedure

Question

Probably an easy-to-answer question. I have this procedure:

CREATE PROCEDURE [dbo].[AccountExists]
    @UserName nvarchar(16)
AS
IF EXISTS (SELECT Id FROM Account WHERE UserName=@UserName)
SELECT 1
ELSE SELECT 0 

When I have ADO.NET code that calls this procedure and does this:

return Convert.ToBoolean(sproc.ExecuteScalar());

Either true or false is returned.

When I change the stored procedure to RETURN 1 or 0 instead of SELECT:

ALTER PROCEDURE [dbo].[AccountExists]
    @UserName nvarchar(16)
AS
IF EXISTS (SELECT Id FROM Account WHERE UserName=@UserName)
RETURN 1
ELSE RETURN 0 

sproc.ExecuteScalar() returns null. If I try sproc.ExecuteNonQuery() instead, -1 is returned.

How do I get the result of a stored procedure with a RETURN in ADO.NET?

I need AccountExists to RETURN instead of SELECT so I can have another stored procedure call it:

--another procedure to insert or update account

DECLARE @exists bit

EXEC @exists = [dbo].[AccountExists] @UserName 

IF @exists=1
--update account
ELSE
 --insert acocunt

Answer 1

Add a parameter, using ParameterDirection.ReturnValue. The return value will be present in the paramter after the execution.