InvalidOperationException - When executing a command, parameters must be exclusively database parameters or values

Question

When I run the following ExecuteStoreQuery consitently throws an InvalidOperationException with the following message:

When executing a command, parameters must be exclusively database parameters or values.

        SqlParameter param1 = new SqlParameter("@pNetworkHealthAssessmentID", pNetworkHealthAssessmentID);
        SqlParameter param2 = new SqlParameter("@pRiskAssessmentQuestionsID", pRiskAssessmentQuestionsID);
        SqlParameter param3 = new SqlParameter("@pUserID", UserID);
        SqlParameter param4 = new SqlParameter("@pSortOrder", pSortOrder);

        var result = db.Database.ExecuteSqlCommand("sp_RiskAssessmentQuestion_Copy2Network @pNetworkHealthAssessmentID, @pRiskAssessmentQuestionsID, @pUserID", "@pSortOrder",
                                      param1, param2, param3, param4)

The Stored procedure's arguments look like this:

 CREATE PROCEDURE [dbo].[sp_RiskAssessmentQuestion_Copy2Network] (
 @pNetworkHealthAssessmentID bigint
,@pRiskAssessmentQuestionsID bigint
,@pUserID bigint
,@pSortOrder int
)

Stored Procedure doesn't return any value back. Why am I getting that exception? I have tried every possible solution but so far unsuccessful

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), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Jan 12, 2019 at 7:29

Igor · Accepted Answer · 2017-04-09 10:29:21Z

9

You are not including that last parameter, "pSortOrder" was separated by a comma and it should be a part of the first string.

var result = db.Database.ExecuteSqlCommand("sp_RiskAssessmentQuestion_Copy2Network @pNetworkHealthAssessmentID, @pRiskAssessmentQuestionsID, @pUserID, @pSortOrder"
, param1, param2, param3, param4)

answered Apr 9, 2017 at 10:29

Igor

62.5k10 gold badges111 silver badges181 bronze badges

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

2 Comments

Ahmed Mujtaba Over a year ago

Ohh I see what I'm doing wrong. Spent hours on this and didn't notice it.

Igor Over a year ago

@AhmedMujtaba - glad it helped. Please consider marking it as answer if it solved your problem.

Collectives™ on Stack Overflow

InvalidOperationException - When executing a command, parameters must be exclusively database parameters or values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related