Click here to Skip to main content
15,881,588 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 :
SQL
<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#:
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.
SQL
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 :
SQL
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 :
C#
cmd.Parameters.Add("@returnValue", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
                   cmd.ExecuteNonQuery();
                   string counter = (string)cmd.Parameters["@returnValue"].Value;


What am i doing wrong?
Posted
Updated 9-Sep-19 4:36am

Based o MSDN documentation (Return Data from a Stored Procedure - SQL Server | Microsoft Docs[^])...

If you would like to return value from stored procedure, you have to set value to the output parameter:

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

AS
BEGIN
	
	SET NOCOUNT ON;
  
	SELECT @returnValue = MAX(CAST(SUBSTRING(Linked_Sun_Id, 4, LEN(Linked_Sun_Id)-3) AS VARCHAR(50))) FROM BrokerMappings WHERE Linked_Sun_Id LIKE 'AG%'
	
END


Then, you'll be able to get value returned by SP.
 
Share this answer
 
Comments
Tshumore 9-Sep-19 9:20am    
I have changed my SP as above. In code i no longer get DBNull exception, i can retrieve the value but it reterieves a 0.
MadMyche 9-Sep-19 9:24am    
What value do you get if you run that query in SSMS?
Maciej Los 9-Sep-19 9:28am    
So, you have to check out what why this happens.
MadMyche 9-Sep-19 9:25am    
+5
Maciej Los 9-Sep-19 9:26am    
Thank you.
The problem is probably your data: run that SELECT query directly in SSMS without the MAX and see what results you get - it you return Linked_sun_Id as well, it should be easier to track down what data is invalid.

Chances are that at least one of the values you are trying to convert to an integer isn't convertible, the substring doesn't work for all ID values. That's kinda confirmed by the result when you try to return a VARCHAR and get NULL values.
 
Share this answer
 
Comments
Tshumore 9-Sep-19 7:42am    
Runnning the query as-is without the MAX, i get the integer parts only i.e 123, 768, 769. I have changed the code to
int counter = Convert.ToInt32(cmd.Parameters["@returnValue"].Value); and indeed i still get Object cannot be cast from dbnull to other types
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
SQL
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
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;

   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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900