How can I specify parameters when calling a stored procedure with Database.ExecuteSqlCommand

Question

I am trying the following:

var sql = @"dbo.sp_ins_test, @Title, @ExamId, @TopicId";        
                List<SqlParameter> parameterList = new List<SqlParameter>();
                parameterList.Add(new SqlParameter ("Title", "Sample Test: " + testNumber));
                parameterList.Add(new SqlParameter ("ExamId", examId));
                parameterList.Add(new SqlParameter ("TopicId",  testTopicId));
                SqlParameter[] parameters = parameterList.ToArray();
 int result = db.Database.ExecuteSqlCommand(sql, parameters);

But it gives an error:

stackTrace=   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__58()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__57()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreCommand(TransactionalBehavior transactionalBehavior, String commandText, Object[] parameters)
   at System.Data.Entity.Internal.InternalContext.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
   at System.Data.Entity.Database.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)
   at System.Data.Entity.Database.ExecuteSqlCommand(String sql, Object[] parameters)
   at WebRole1.Controllers.TestController.<Post>d__1d.MoveNext() in c:\K\ S362\WebRole1\Controllers\Web API - Data\TestController.cs:line 186
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Threading.Tasks.TaskHelpersExtensions.<CastToObject>d__3`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Web.Http.Controllers.AuthenticationFilterResult.<ExecuteAsync>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()

Can anyone confirm if this is a correct way to call a stored procedure with parameters. The error does not seem very clear so I am rather confused.

http://stackoverflow.com/questions/5474264/how-to-pass-parameters-to-the-dbcontext-database-executesqlcommand-method?answertab=votes#tab-top — Habib, Jun 26 '14 at 15:50
Take the first comma out: `var sql = @"dbo.sp_ins_test @Title, @ExamId, @TopicId";` — Marc Gravell, Jun 26 '14 at 16:00
btw; with dapper, this would be just: `connection.Execute("sp_ins_test", new { Title = "Sample Test: " + testNumber, ExamId = examId, TopicId = testTopicId }, commandType: CommandType.StoredProcedure);` — Marc Gravell, Jun 26 '14 at 16:02

score 2 · Accepted Answer · answered Jun 26 '14 at 16:08

The issue is simply an extra comma. Remove it:

var sql = @"dbo.sp_ins_test @Title, @ExamId, @TopicId";

While you're there, you could probably also make this a const string instead of a var, but thanks to string-interning this won't really matter much. The compiler might even do it for you.

score 1 · Answer 2 · edited May 23 '17 at 11:57

1

Your error is in the naming of parameters - they should begin with @

            parameterList.Add(new SqlParameter ("@Title", "Sample Test: " + testNumber));
            parameterList.Add(new SqlParameter ("@ExamId", examId));
            parameterList.Add(new SqlParameter ("@TopicId",  testTopicId));

SQL output parameters in C#

edited May 23 '17 at 11:57

Community

1
1

answered Jun 26 '14 at 15:50

Eugene Podskal

10,270
5
31
53

Actually, ADO.NET doesn't care whether you call a `SqlParameter` `"@Foo"` or `"Foo"` - it is treated identically; I would **not** expect this to fix anything – Marc Gravell Jun 26 '14 at 15:58
Well, I haven't tried to use it without @, but all [previous](http://stackoverflow.com/questions/24408580/sql-output-parameters-in-c-sharp/24408822) [questions](http://stackoverflow.com/questions/10245510/is-it-necessary-to-add-a-in-front-of-an-sqlparameter-name) and [docs](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.parametername.aspx) suggest that it is the right way. – Eugene Podskal Jun 26 '14 at 16:01
I am getting an error "exceptionMessage=Incorrect syntax near ','." – Alan2 Jun 26 '14 at 16:08
@EugenePodskal you have misunderstood the first of those; the problem is that whatever you call it: you must *keep* calling it. In the first example, they *add* it as `"@logID"`, but later access it via the indexer and `"logID"`. Using `"logID"` in both places would have been fine. Re the docs: frankly, they're misleading to the point of being wrong, as ADO.NET supports more than just sql server. In reality, all implementations work fine with or without any leading character - and working without is recommended for libraries, for cross-rdbms reasons. – Marc Gravell Jun 26 '14 at 16:08
@MarcGravell I agree. It is logical. Just that was the first thing that got my attention. And I have glanced above the unneeded coma. – Eugene Podskal Jun 26 '14 at 16:13

How can I specify parameters when calling a stored procedure with Database.ExecuteSqlCommand

2 Answers2