4
Answers

Passing results from Stored Procedure to int array

Marius Vasile

Marius Vasile
Oct 22
324
1

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

Answers (4)
Next Recommended Forum