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
private void CreateChart()
{
string RevenueString = string.Empty;
int Year = 0;
Year = 2020;
SqlConnection SQLCONNECT = new SqlConnection(sSQLConnection);
try
{
if (SQLCONNECT.State == ConnectionState.Closed)
{
SQLCONNECT.Open();
}
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.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..