Use if statement and return value to c# from a stored procedure in SQL Server

Question

All I want to do is check if a name exists in a given table in my database or not but I am getting multiple returns from the stored procedure causing an exception.

This is the stored procedure I am using in SQL Server:

CREATE OR ALTER PROCEDURE ContactExists 
    @Name varchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @result bit = 0
    DECLARE @name varchar(50) = '';

    SET @name = (SELECT Name FROM Table)

    IF (@name = @Name)
    BEGIN
        SET @result = 1
        RETURN @result
    END

    RETURN @result
END
GO

And this is the method I am using in C#:

SPstr = "dbo.ContactExists";

SqlCommand cmd = new SqlCommand(SPstr, connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", Name);

var returnValue = cmd.Parameters.Add("@result", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery();

return (int)returnValue.Value;

Answer 1

Don't use the return value from stored procedures to return data. It's an old and mostly obsolete way to signal success or failure of the proc. So either use an output parameter:

CREATE OR ALTER PROCEDURE ContactExists 
    @Name varchar(50), @result bit output
AS
BEGIN
    SET NOCOUNT ON;

    set @result = 0;
    
    if exists (SELECT * FROM SomeTable where name = @Name)
    begin
      set @result = 1;
    end


END

or a resultset

CREATE OR ALTER PROCEDURE ContactExists 
    @Name varchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    declare @result bit = 0;
    
    if exists (SELECT * FROM SomeTable where name = @Name)
    begin
      set @result = 1;
    end

    select @result result;
END

Answer 2

I am getting multiple returns from the stored procedure causing an exception.

I suspect you mean you're getting this:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

which is going to arise from this:

set @name = (SELECT Name from Table)

if Table has more than one row. Ensure the subquery (SELECT Name from Table) returns at most one row (use a WHERE on the pk, use MAX etc..)