Pass integer array to the SQL Server stored procedure

Question

Well this well answered question however I can't get it right for me. What I am trying to do call a stored procedure from .NET Core project using Entity Framework with some parameters. One of those parameter should be array (which I consider table type in SQL Server by create a custom table data type) type. I followed this Stackoverflow link. But got an error when I tried to execute my SQL command.

Here is my code:

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));

foreach (var section in model.VMSectionIds) //model.VMSectionIds contains set of integers
{
    dt.Rows.Add(section);
}

and finally I call stored procedure like this:

var sectiolist = new SqlParameter("@Sections", SqlDbType.Structured)
            {
                TypeName = "[dbo].[SectionList]",
                Value = dt
            };
_db.ExecuteSqlCommand("EXEC [SP_GenerateRegularEmployeeSalary] "+mastermodel.ID+","+ fromdate + "," + todate + ",1," + sectiolist + ""); //don't worry I took care of SQL injection for others parameter

But this execution throws an exception

SqlException: Must declare the scalar variable "@Sections"

I can't figure it out where exact problem is. Here call of stored procedure (with some static test parameter) from SQL for clear understanding of my stored procedure call mechanism:

DECLARE @data [SectionList]
INSERT @data (Id) VALUES (2, 3)

EXEC [SP_GenerateRegularEmployeeSalary] 2,'20190401','20190430','1',@data

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jun 08 '19 at 07:18

George Mahusay · Answer 1 · 2019-06-08T07:20:47.640

1

he using ExecuteSqlCommand incorrectly. he should not used string concatenation to avoid SQL Injection attacks in th application

_db.ExecuteSqlCommand("EXEC SP_GenerateRegularEmployeeSalary @YOUR_PARAM_ON_STOREPROCEDURE", sectiolist);

edited Jun 08 '19 at 07:20

answered Jun 08 '19 at 06:34

George Mahusay

51
3

1

Any explanation what this approach does differently, and why this solves the problem? – marc_s Jun 08 '19 at 07:02
he using ExecuteSqlCommand incorrectly. he should not used string concatenation to avoid SQL Injection attacks in th application – George Mahusay Jun 08 '19 at 07:14
Put that explanation in your answer! (I totally agree with it, btw) – marc_s Jun 08 '19 at 07:19
Thanks, I got it now but I can only one as correct answer, hopefully you provide little bit explanation with answer for make it accepted one. :) – Parvez Jun 08 '19 at 08:08

Flavio Francisco · Accepted Answer · 2019-06-08T07:17:14.080

Looks that you are using the ExecuteSqlCommand incorrectly. Try this way and don't use string concatenation in your code to avoid SQL Injection attacks in your application. Read more about it here.

Also put the correct expected parameter names from the stored procedure: SP_GenerateRegularEmployeeSalary.

Option 1

_db.ExecuteSqlCommand("EXEC [SP_GenerateRegularEmployeeSalary] @ID, @FromDate, @ToDate, @Flag, @Sections", 
   new SqlParameter("@ID", mastermodel.ID),
   new SqlParameter("@FromDate", fromdate),
   new SqlParameter("@ToDate", todate),
   new SqlParameter("@Flag", 1),
   new SqlParameter("@Sections", sectiolist));

Option 2

_db.ExecuteSqlCommand("EXEC [SP_GenerateRegularEmployeeSalary] @ID = {0}, @FromDate = {1}, @ToDate = {2}, @Flag = 1, @Sections = {4}", mastermodel.ID, fromdate, todate, sectiolist);

Please read this documentation about this method.

Pass integer array to the SQL Server stored procedure

2 Answers2