C# SQL stored proc with return param

Question

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 22-Apr-20 10:30am

DumbCoderX

Updated 22-Apr-20 16:09pm

Add a Solution

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

MadMyche 23-Apr-20 8:13am

You are correct that it it is provided; however, there is no value set for it in the calling code AND there is no default value in the SProc. I will update my answer with how a fix inside this

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MadMyche · Accepted Answer · 2020-04-22T16:10:00

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