Passing results from Stored Procedure to int array

Marius Vasile

I have the following Stored procedure

CREATE PROCEDURE [dbo].[RALAM]

@CurrentYear as int,
@Spec as nvarchar(10)

AS 

	DECLARE @a1 int,@a2 int,@a3 int,@a4 int,@a5 int,@a6 int,@a7 int,@a8 int,@a9 int,@a10 int,@a11 int,@a12 int
	DECLARE @v1 int,@v2 int,@v3 int,@v4 int,@v5 int,@v6 int,@v7 int,@v8 int,@v9 int,@v10 int,@v11 int,@v12 int
	DECLARE @result1 int,@result2 int,@result3 int,@result4 int,@result5 int,@result6 int,@result7 int,@result8 int,@result9 int,@result10 int,@result11 int,@result12 int

	SET @a1 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 1 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a2 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 2 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a3 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 3 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a4 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 4 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a5 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 5 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a6 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 6 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a7 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 7 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a8 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 8 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a9 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 9 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a10 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 10 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a11 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 11 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a12 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 12 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);

	SET @v1 = (SELECT SUM(CASE When Month = 'January' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v2 = (SELECT SUM(CASE When Month = 'February' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v3 = (SELECT SUM(CASE When Month = 'March' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v4 = (SELECT SUM(CASE When Month = 'April' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v5 = (SELECT SUM(CASE When Month = 'May' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v6 = (SELECT SUM(CASE When Month = 'June' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v7 = (SELECT SUM(CASE When Month = 'July' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v8 = (SELECT SUM(CASE When Month = 'August' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v9 = (SELECT SUM(CASE When Month = 'September' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v10 = (SELECT SUM(CASE When Month = 'October' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v11 = (SELECT SUM(CASE When Month = 'November' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v12 = (SELECT SUM(CASE When Month = 'December' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);

	BEGIN
		SET @result1 = @a1*200000/@v1;
		SET @result2 = ((@a1+@a2)*200000)/(@v1+@v2);
		SET @result3 = ((@a1+@a2+@a3)*200000)/(@v1+@v2+@v3);
		SET @result4 = ((@a1+@a2+@a3+@a4)*200000)/(@v1+@v2+@v3+@v4);
		SET @result5 = ((@a1+@a2+@a3+@a4+@a5)*200000)/(@v1+@v2+@v3+@v4+@v5);
		SET @result6 = ((@a1+@a2+@a3+@a4+@a5+@a6)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6);
		SET @result7 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7);
		SET @result8 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8);
		SET @result9 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8+@a9)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8+@v9);
		SET @result10 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8+@a9+@a10)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8+@v9+@v10);
		SET @result11 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8+@a9+@a10+@a11)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8+@v9+@v10+@v11);
		SET @result12 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8+@a9+@a10+@a11+@a12)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8+@v9+@v10+@v11+@v12);

	END

and I want to pass the @results to int array. I tried with

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CurrentYear", currentYear);
cmd.Parameters.AddWithValue("@spec", spec);
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        int[] yValues =
        {
            Convert.ToInt32(rdr["Result1"].ToString()),
            Convert.ToInt32(rdr["Result2"].ToString()),
            Convert.ToInt32(rdr["Result3"].ToString()),
            Convert.ToInt32(rdr["Result4"].ToString()),
            Convert.ToInt32(rdr["Result5"].ToString()),
            Convert.ToInt32(rdr["Result6"].ToString()),
            Convert.ToInt32(rdr["Result7"].ToString()),
            Convert.ToInt32(rdr["Result8"].ToString()),
            Convert.ToInt32(rdr["Result9"].ToString()),
            Convert.ToInt32(rdr["Result10"].ToString()),
            Convert.ToInt32(rdr["Result11"].ToString()),
            Convert.ToInt32(rdr["Result12"].ToString())
        };
    }
}

but is not working. How it should be done, please

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty1y ago

Accepted answer

You need to return the calculated values using SELECT statements in your stored procedure:

CREATE PROCEDURE [dbo].[RALAM]

	@CurrentYear as int,
	@Spec as nvarchar(10)

AS
BEGIN
	DECLARE @a1 int,@a2 int,@a3 int,@a4 int,@a5 int,@a6 int,@a7 int,@a8 int,@a9 int,@a10 int,@a11 int,@a12 int
	DECLARE @v1 int,@v2 int,@v3 int,@v4 int,@v5 int,@v6 int,@v7 int,@v8 int,@v9 int,@v10 int,@v11 int,@v12 int
	DECLARE @result1 int,@result2 int,@result3 int,@result4 int,@result5 int,@result6 int,@result7 int,@result8 int,@result9 int,@result10 int,@result11 int,@result12 int

	SET @a1 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 1 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a2 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 2 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a3 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 3 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a4 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 4 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a5 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 5 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a6 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 6 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a7 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 7 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a8 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 8 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a9 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 9 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a10 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 10 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a11 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 11 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);
	SET @a12 = (SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 12 Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec);

	SET @v1 = (SELECT SUM(CASE When Month = 'January' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v2 = (SELECT SUM(CASE When Month = 'February' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v3 = (SELECT SUM(CASE When Month = 'March' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v4 = (SELECT SUM(CASE When Month = 'April' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v5 = (SELECT SUM(CASE When Month = 'May' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v6 = (SELECT SUM(CASE When Month = 'June' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v7 = (SELECT SUM(CASE When Month = 'July' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v8 = (SELECT SUM(CASE When Month = 'August' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v9 = (SELECT SUM(CASE When Month = 'September' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v10 = (SELECT SUM(CASE When Month = 'October' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v11 = (SELECT SUM(CASE When Month = 'November' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);
	SET @v12 = (SELECT SUM(CASE When Month = 'December' Then Value Else 0 End) FROM tblOmOra WHERE Year=@CurrentYear);

	SET @result1 = @a1*200000/@v1;
	SET @result2 = ((@a1+@a2)*200000)/(@v1+@v2);
	SET @result3 = ((@a1+@a2+@a3)*200000)/(@v1+@v2+@v3);
	SET @result4 = ((@a1+@a2+@a3+@a4)*200000)/(@v1+@v2+@v3+@v4);
	SET @result5 = ((@a1+@a2+@a3+@a4+@a5)*200000)/(@v1+@v2+@v3+@v4+@v5);
	SET @result6 = ((@a1+@a2+@a3+@a4+@a5+@a6)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6);
	SET @result7 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7);
	SET @result8 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8);
	SET @result9 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8+@a9)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8+@v9);
	SET @result10 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8+@a9+@a10)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8+@v9+@v10);
	SET @result11 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8+@a9+@a10+@a11)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8+@v9+@v10+@v11);
	SET @result12 = ((@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8+@a9+@a10+@a11+@a12)*200000)/(@v1+@v2+@v3+@v4+@v5+@v6+@v7+@v8+@v9+@v10+@v11+@v12);
		
	-- Return the results
    SELECT @result1 AS Result1, @result2 AS Result2, @result3 AS Result3, @result4 AS Result4, 
           @result5 AS Result5, @result6 AS Result6, @result7 AS Result7, @result8 AS Result8, 
           @result9 AS Result9, @result10 AS Result10, @result11 AS Result11, @result12 AS Result12;

END

Marius Vasile1y ago
There is no difference between my rdr and "for" reader. I guess there is something wrong with calculation, probably with @v if is null

Amit Mohanty1y ago

Modify the C# Code to Read the Results.

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CurrentYear", currentYear);
cmd.Parameters.AddWithValue("@spec", spec);
conn.Open();

using (SqlDataReader rdr = cmd.ExecuteReader())
{
    if (rdr.Read())
    {
		decimal[] yValues = new decimal[12];
		for (int i = 0; i < 12; i++)
		{
			yValues[i] = Convert.ToDecimal(rdr[$"Result{i + 1}"]);
		}
    }
}

Marius Vasile1y ago
Thank you Amit, did that, I forgot to copy from stored procedure, also changed type of @result1-12 from int to decimal(6,2), still I don't have the results in the array