The problem that you originally posted is that your stored procedure is going to return a result-set and not a populated value; while you declare the value you never set it. This is the remedy provided in Answer #2
Now there is an alternative, as this is returning a result set and there is only 1 value in that set that is needed; you can simply remove the output parameter and utilzie the ExecuteScalar() method
ALTER PROCEDURE [dbo].[spGetHighestAGCode]
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
using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
{
conn.Open();
SqlCommand cmd = new SqlCommand("spGetHighestAGCode", conn);
cmd.CommandType = CommandType.StoredProcedure;
int counter = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
One thing to not is that I did not use
(int)(cmd.ExecuteScalar())
and opted to use the
Convert.ToInt32()
method, which will not be susceptible to having an empty return set. It will; however, return a null reference exception if the value being returned is null