The procedure doesn't return a value so you could use a function instead. For more information see
CREATE FUNCTION[
^].
Of course one possibility would be to combine the procedures for example something like:
create PROCEDURE dbo.SPonDeptTable
(
@Option int,
@CompName varchar(50)
)
AS
BEGIN
IF @Option=0 BEGIN
SELECT DeptName
FROM MstDepartment
WHERE CompanyID = (SELECT CompanyID
FROM MstCompany
WHERE CompanyName=@CompName);
END
RETURN
END