Call stored procedure using ExecuteSqlCommand (expects parameters which was not supplied)

Question

I'm trying to call a stored procedure from EF using context.Database.ExecuteSqlCommand since one of my parameters is a datatable.

Here are the procedure's parameters :

ALTER PROCEDURE [mySchema].[myProc]
    @customerId INT,
    @indicatorTypeId INT,
    @indicators [mySchema].[IndicatorList] READONLY,
    @startDate DATETIME,
    @endDate DATETIME

and here is the c# code calling the stored procedure :

var indicatorsDt = new DataTable();

indicatorsDt.Columns.Add("Date", typeof(DateTime));
indicatorsDt.Columns.Add("Ongoing", typeof(int));
indicatorsDt.Columns.Add("Success", typeof(int));
indicatorsDt.Columns.Add("Warning", typeof(int));
indicatorsDt.Columns.Add("Error", typeof(int));
indicatorsDt.Columns.Add("Other", typeof(int));

var customerIdParam = new SqlParameter("customerId", SqlDbType.Int);
customerIdParam.Value = customerId;

var typeIdParam = new SqlParameter("indicatorTypeId", SqlDbType.Int);
typeIdParam.Value = typeId;

var startDateParam = new SqlParameter("startDate", SqlDbType.DateTime);
startDateParam.Value = startDate;

var endDateParam = new SqlParameter("endDate", SqlDbType.DateTime);
endDateParam.Value = endDate;

foreach (var indicator in indicators)
{
    indicatorsDt.Rows.Add(indicator.Date, indicator.Ongoing, 
                          indicator.Success, indicator.Warning, 
                          indicator.Error, indicator.Other);
}

var tableParameter = new SqlParameter("indicators", SqlDbType.Structured);
tableParameter.Value = indicatorsDt;
tableParameter.TypeName = "MySchema.IndicatorList";

context.Database.ExecuteSqlCommand("exec MySchema.MyProc", customerIdParam, typeIdParam, tableParameter, startDateParam, endDateParam);

As you can see, all parameters are provided, none of them has a null value but I always get this SqlException :

Procedure or function 'UpdateIndicators' expects parameter '@customerId', which was not supplied.

I can't figure out what I am missing. Is the use of SqlParameter wrong ? Parameters are supplied in the same order in the ExecuteSqlCommand even if it is not important.

Thank in advance.

strickt01 · Accepted Answer · 2015-11-25 12:50:20Z

7

You are missing the parameters in the SQL string you are executing. Try creating your parameters with an "@" preceding the name and then changing the ExecuteSqlCommand call to:

context.Database.ExecuteSqlCommand("exec MySchema.MyProc @customerId, @indicatorTypeId, @indicators, @startDate, @endDate", customerIdParam, typeIdParam, tableParameter, startDateParam, endDateParam);

edited Nov 25, 2015 at 12:50

answered Nov 25, 2015 at 11:02

strickt01

4,0581 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mill · Accepted Answer · 2018-10-19 09:34:02Z

3

This works:

var bookIdParameter = new SqlParameter();
bookIdParameter.ParameterName = "@BookId";
bookIdParameter.Direction = ParameterDirection.Output;
bookIdParameter.SqlDbType = SqlDbType.Int;
var authors = context.Database.ExecuteSqlCommand("usp_CreateBook @BookName, @ISBN, @BookId OUT",
    new SqlParameter("@BookName", "Book"),
    new SqlParameter("@ISBN", "ISBN"),
    bookIdParameter);

answered Oct 19, 2018 at 9:34

Mill

1421 silver badge6 bronze badges

Comments

FrankO · Accepted Answer · 2016-02-02 05:41:28Z

2

Here is how I currently call my stored procedures with parameters. Replace the parameter names/values with your own. Keep in mind, you may have to list the parameters in the same order as declared in the stored procedure.

Create a SqlParameter array:

SqlParameter[] parameters = new SqlParameter[] {
     new SqlParameter("@ParamName1", Value1),
     new SqlParameter("@ParamName2", Value2),
     new SqlParameter("@ParamName3", Value3),
     new SqlParameter("@ParamName4", Value4),
     new SqlParameter("@ParamName5", Value5)
};

Then call your stored procedure:

 _context.Database.ExecuteSqlCommand(DbHelper.GenerateCommandText("SpName", parameters), parameters);

Here is the DbHelper code:

public class DbHelper
{
    public static string GenerateCommandText(string storedProcedure, SqlParameter[] parameters)
    {
        string CommandText = "EXEC {0} {1}";
        string[] ParameterNames = new string[parameters.Length];

        for (int i = 0; i < parameters.Length; i++)
        {
            ParameterNames[i] = parameters[i].ParameterName;
        }

        return string.Format(CommandText, storedProcedure, string.Join(",", ParameterNames));
    }
}

answered Feb 2, 2016 at 5:41

FrankO

2,5726 gold badges27 silver badges35 bronze badges

1 Comment

user692942 Over a year ago

Slight improvement to allow passing parameters without first declaring a SqlParameter array: public static string GenerateCommandText(string storedProcedure, params SqlParameter[] parameters).

Xavier W. · Accepted Answer · 2015-11-25 10:39:15Z

0

Try with this :

var customerIdParam = new SqlParameter("@customerId", SqlDbType.Int);

answered Nov 25, 2015 at 10:39

Xavier W.

1,3604 gold badges22 silver badges49 bronze badges

1 Comment

Jerome Lecerf Over a year ago

No, it's not working either, i get the same Exception.

Collectives™ on Stack Overflow

Call stored procedure using ExecuteSqlCommand (expects parameters which was not supplied)

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related