How to check if sproc returns no results

Question

I have a stored procedure that takes in a Department Name and returns the Department Id in the form of an int. I'm trying to use the procedure to determine if a department with the specified name already exists. How can I accomplish this? If the sproc returns no results, what is the value represented by? Is it null? Should I possibly be using a separate sproc to determine if the department already exists? Here is my current sproc:

    @Department_Name varchar(100),
    @Id int OUTPUT
AS
    SET NOCOUNT ON
SELECT @Id = Id FROM Department
WHERE Name = @Department_Name
    RETURN @Id

Answer 1

"There are two ways of returning result sets or data from a procedure to a calling program: output parameters and return codes"

Return Data from a Stored Procedure

Stored Procedures - Output Parameters & Return Values

Answer 2

Return values can be used within stored procedures to provide the stored procedure execution status to the calling program. The return values -99 through 0 are reserved for SQL Server internal use. You can create your own parameters that can be passed back to the calling program. By default, the successful execution of a stored procedure (or any group of SQL statements) will return 0.

You can return some value to indicate that your @Id is null. And how to get value which you return from SP in ASP you can read here: https://web.archive.org/web/20211020103538/https://www.4guysfromrolla.com/webtech/tips/t100901-1.shtml Or you can refer to this answer: https://stackoverflow.com/a/2342862/1048105