Click here to Skip to main content
15,921,941 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
After many many years of being out of tech im trying once again to learn to code.. This time with C# (setting the scene so you dont laugh)

Im really struggling with bringing back a return param from a SQL Stored Proc within my C# code..
Hoping you guys can help..

My Stored Proc
(And yes I know its not very efficient but this isnt my issue and ill tidy that up later once ive proven the basic concept)

Alter PROCEDURE [dbo].[GetFinancialTargetPerformance_12Month] @Year INT, @ReturnVal VarChar(1000) OUTPUT 

AS

Declare @Month1 as varchar(20);
Declare @Month2 as varchar(20);
Declare @Month3 as varchar(20);
Declare @Month4 as varchar(20);
Declare @Month5 as varchar(20);
Declare @Month6 as varchar(20);
Declare @Month7 as varchar(20);
Declare @Month8 as varchar(20);
Declare @Month9 as varchar(20);
Declare @Month10 as varchar(20);
Declare @Month11 as varchar(20);
Declare @Month12 as varchar(20);



Set @Month1 =  (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 1 AND DATEPART(year, WI_Date) = @Year) 
Set @Month2 =  (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 2 AND DATEPART(year, WI_Date) = @Year)
Set @Month3 =  (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 3 AND DATEPART(year, WI_Date) = @Year)
Set @Month4 =  (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 4 AND DATEPART(year, WI_Date) = @Year)
Set @Month5 =  (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 5 AND DATEPART(year, WI_Date) = @Year)
Set @Month6 =  (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 6 AND DATEPART(year, WI_Date) = @Year)
Set @Month7 =  (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 7 AND DATEPART(year, WI_Date) = @Year)
Set @Month8 =  (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 8 AND DATEPART(year, WI_Date) = @Year)
Set @Month9 =  (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 9 AND DATEPART(year, WI_Date) = @Year)
Set @Month10 = (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 10 AND DATEPART(year, WI_Date) = @Year)
Set @Month11 = (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 11 AND DATEPART(year, WI_Date) = @Year)
Set @Month12 = (Select SUM(CAST(Revenue AS MONEY)) From Work_Items  Where DATEPART(month, WI_Date) = 12 AND DATEPART(year, WI_Date) = @Year)

IF(@Month1 is Null) SET @Month1 = '0.00'
IF(@Month2 is Null) SET @Month2 = '0.00'
IF(@Month3 is Null) SET @Month3 = '0.00'
IF(@Month4 is Null) SET @Month4 = '0.00'
IF(@Month5 is Null) SET @Month5 = '0.00'
IF(@Month6 is Null) SET @Month6 = '0.00'
IF(@Month7 is Null) SET @Month7 = '0.00'
IF(@Month8 is Null) SET @Month8 = '0.00'
IF(@Month9 is Null) SET @Month9 = '0.00'
IF(@Month10 is Null) SET @Month10 = '0.00'
IF(@Month11 is Null) SET @Month11 = '0.00'
IF(@Month12 is Null) SET @Month12 = '0.00'

Set @ReturnVal = ( CONCAT( @Month1 + ',', @Month2 + ',', @Month3 + ',',  @Month4 + ',' ,  @Month5 + ',' ,  @Month6 + ',' ,  @Month7 + ',' ,  @Month8 + ',' ,  @Month9 + ',', @Month10 + ',' , @Month11 + ',' ,  @Month12 ))

Select @ReturnVal




My C# Calling code

C#
private void CreateChart()
        {
            string RevenueString = string.Empty;
            int Year = 0;

            //Grab Year - Hardcoded for now (TO DO) 
            Year = 2020;




 SqlConnection SQLCONNECT = new SqlConnection(sSQLConnection);


            try
            {
                if (SQLCONNECT.State == ConnectionState.Closed)
                {
                    SQLCONNECT.Open();

//My connection opens so this isnt the issue...(ive not included my connection string but its good and works..
                }

                SqlCommand cmd = new SqlCommand("dbo.GetFinancialTargetPerformance_12Month", SQLCONNECT);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Year", Year);
                
                SqlParameter RuturnValue = new SqlParameter("@ReturnVal", SqlDbType.VarChar);
                RuturnValue.Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@ReturnVal", System.Data.SqlDbType.VarChar).Direction = System.Data.ParameterDirection.ReturnValue;

//Here lies the issue... The next command just bombs out with a generic error message 
                cmd.ExecuteNonQuery();

                RevenueString = (string)cmd.Parameters["@ReturnVal"].Value;
            }
            catch
            {

            }
            finally
            {
                SQLCONNECT.Close();
            }





Thanks for taking the time to read...



What I have tried:

Various Articles But to no avail..
Posted
Updated 22-Apr-20 16:09pm
Comments
ZurdoDev 22-Apr-20 16:52pm    
My personal preference is not to use output parameters but rather just use a select statement.

Then you can use command.ExecuteScalar() if only one row and one column is returned or you can use ExecuteReader() and get a DataReader and access all returned fields and rows.
MadMyche 22-Apr-20 21:53pm    
This SP actually ends with a SELECT statement
ZurdoDev 23-Apr-20 7:27am    
Yes, but the error is related to the Output parameter.
MadMyche 22-Apr-20 22:09pm    
Kinda curious what the generic error message... Some code in the Catch block would most likely help
DumbCoderX 23-Apr-20 3:39am    
When ive dug into the exception its a little more meaningful than the one that was reported origioanlly (presumably rolled up somehow)

The Exception Message =
"Procedure or function 'GetFinancialTargetPerformance_12Month' expects parameter '@ReturnVal', which was not supplied."


But From my code I believe it is provided

Confused.com

1 solution

You may want to try this out for the C# call
C#
   SqlCommand cmd = new SqlCommand("dbo.GetFinancialTargetPerformance_12Month", SQLCONNECT);

   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.AddWithValue("@Year", Year);

   SqlParameter RuturnValue = new SqlParameter("@ReturnVal", SqlDbType.VarChar);
   RuturnValue.Direction = ParameterDirection.Output;

// cmd.Parameters.Add("@ReturnVal", System.Data.SqlDbType.VarChar).Direction = System.Data.ParameterDirection.ReturnValue;
   cmd.Parameters.Add(RuturnValue);

// Here lies the issue... The next command just bombs out with a generic error message 
   cmd.ExecuteNonQuery();

// RevenueString = (string)cmd.Parameters["@ReturnVal"].Value;
   RevenueString = (string)RuturnValue.Value;
Or another option that you have presented is that the last statement in our stored procedure is
Select @ReturnVal
So in all reality you can use the ExecuteScalar method in your calling code, shrinking this way down
C#
SqlCommand cmd = new SqlCommand("dbo.GetFinancialTargetPerformance_12Month", SQLCONNECT);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Year", Year);
cmd.Parameters.AddWithValue("@ReturnVal", string.Empty);
RevenueString = (string)cmd.ExecuteScalar();

Update based on the actual error message now in the comments

There are 2 parameters defined in the stored procedure and there are 2 defined in the calling code. However, the @ReturnValue does not have a value associated with it in the calling code, but the Procedure is expecting it; thus the message.

I find the easiest way to fix this for output parameters is to set default values for them within the parameter definition portion of the Stored Procedure; something like this should take care of it
SQL
ALTER PROCEDURE dbo.GetFinancialTargetPerformance_12Month (
   @Year      INT,
   @ReturnVal VARCHAR (1000) = '' OUTPUT 
) AS
 
Share this answer
 
v2
Comments
DumbCoderX 27-Apr-20 10:55am    
Thankyou so much..

Idiopathic have to add the param size to the following line

SqlParameter("@ReturnVal", SqlDbType.VarChar,1000);

But other than that it worked a treat..

Thanks again, taught me loads

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