Stored Proc Won't Return Correct Value.

I am using VS 2006, asp.net and C# to call a stored procedure. I want to return a value from the stored
procedure into a variable in my C# code. Currently this is not working for me, and I can not figure out what
the problem is? Can someone please help me out?

I really don't think the problem is in my stored procedure. I can right click on the stored proc and run it with
success. If I trace into the C# code though only a negative one (-1) is returned.

On line 5 I have tried the alternate lines of code but this has not worked for me.

     mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.Output;
     mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.ReturnValue;


Can someone please help me out. I have spent to much time trying to figure this one out.


// C# code to call stored proc.
1  try
2   {
3     SqlCommand mySqlCommand = new SqlCommand("[GetRecordsAssociatedWithRealtor]", mySqlConnection);
4     mySqlCommand.Parameters.Add("@RealtorId", SqlDbType.Decimal, 10).Value = RealtorId;
5     mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.InputOutput;
6     mySqlCommand.CommandType = CommandType.StoredProcedure;
7     RecordsAssociatedWithRealtor = mySqlCommand.ExecuteNonQuery();
8   }

// Stored procedure below.
USE [REALTOR]
GO
/****** Object:  StoredProcedure [dbo].[GetRecordAssociatedWithRealtor]    Script Date: 10/18/2007 13:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetRecordAssociatedWithRealtor]
(
 @RealtorId int,
 @TotalRecords int output
)
AS
 BEGIN
 DECLARE @HouseDetailRecords int
 DECLARE @RealtorRecords int
 SELECT  @HouseDetailRecords= RealtorId from Realtor where RealtorId=@RealtorId
 SELECT  @RealtorRecords = RealtorId from ConstructionDetail where RealtorId=@RealtorId
 SET     @TotalRecords=SUM(@HouseDetailRecords+@RealtorRecords)
 RETURN  @TotalRecords
END