How do I retrieve and bind a stored procedure outputparameter to a variable in C# code

See more:

C#

I am trying to retrieve the highest int number in a mixed var/int column using Stored Procedure output parameter such that for values AG000123, AG000768,AG000769 i want to retrieve 769. When i call this SP in C# code i get invalid cast exception on the int. This is my stored procedure :

<pre lang="SQL">CREATE PROCEDURE [dbo].[GetMaxAGCode]
	
	@returnValue INT OUT 
AS
BEGIN
	
	SET NOCOUNT ON;

SELECT MAX(CAST(SUBSTRING(Linked_Sun_Id, 4, LEN(Linked_Sun_Id)-3) AS INT)) FROM BrokerMappings WHERE Linked_Sun_Id LIKE 'AG%'
END
GO

And this is how im calling the same in C#:

<pre lang="c#"> using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
                {
 conn.Open();

                    SqlCommand cmd = new SqlCommand("spGetHighestAGCode", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;
                    cmd.ExecuteNonQuery();
                    int counter = (int)cmd.Parameters["@returnValue"].Value;
                    conn.Close();
 }

I am able to retrieve the value in SSMS but in my actual result set it returns null i.e Highest index column has NULL.

DECLARE @number INT

EXEC spGetHighestAGCode

@returnValue=@number

SELECT @number AS 'Highest index'

The upper result set in SSMS however returns the value, i wanted to attach a screenshot for clarity but i cannot see option here.

What I have tried:

When i attempt to retrieve the value as a varchar i get DBNull exception when i debug IN C#. I do suspect this is because the line SELECT @number AS 'Highest index' in SSMS returns NULL.It means although i can get the correct output value, i cannot assign it to a variable @number. I have done this as :

ALTER PROCEDURE [dbo].[spGetHighestAGCode]
	
	@returnValue VARCHAR(50) OUT 

AS
BEGIN
	
	SET NOCOUNT ON;

    
	SELECT MAX(CAST(SUBSTRING(Linked_Sun_Id, 4, LEN(Linked_Sun_Id)-3) AS VARCHAR(50))) FROM BrokerMappings WHERE Linked_Sun_Id LIKE 'AG%'
	
END

and :

cmd.Parameters.Add("@returnValue", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
                   cmd.ExecuteNonQuery();
                   string counter = (string)cmd.Parameters["@returnValue"].Value;

What am i doing wrong?