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