10

I use Entity Framework 4.2 and want to call a stored procedure that has input parameters. I'm using Database.ExecuteSqlCommand to call the stored procedure.

However, the documentation is lacking in the correct syntax for the call in order to map the parameters correctly. My google-foo is failing me, and any help will be appreciated.

I.e. I have a procedure 

procedure SetElementFrequency
  @ElementTypeID integer,
  @Frequency float
as ...

I've tried calling it with

Database.ExecuteSqlCommand("exec SetElementFrequency @p0 @p1", 
                            elementType, frequency);

and

Database.ExecuteSqlCommand("exec SetElementFrequency {0} {1}", 
                            elementType, frequency);

but they both fail with the error Incorrect syntax near '@p1'.

SWeko
  • 30,434
  • 10
  • 71
  • 106

3 Answers3

16

Depending on your underlying database provider, you can use either of the following.

Database.ExecuteSqlCommand(
    "exec SetElementFrequency {0}, {1}",
    elementType, frequency);

or

Database.ExecuteSqlCommand("exec SetElementFrequency ?, ?", elementType, frequency);

You may also specify elementType and frequency as DbParameter-based objects to provide your own names via the ParameterName property.

bricelam
  • 28,825
  • 9
  • 92
  • 117
2

Try something like this:

context.Database.ExecuteSqlCommand("delete MasterSmsCampaignCertificateInfo where      MasterSmsCampaignGuid = @p0 and CertificateId = @p1",
TheCampaignGuid,
certInfo.CertificateId);

Take a look at this similar question: ExecuteSqlCommand with output parameter Best regards

Community
  • 1
  • 1
Oscar
  • 13,594
  • 8
  • 47
  • 75
  • So does this mean that the first parameter is set to `@p0`, the second to `@p1`, and so on? – SWeko Jun 20 '12 at 15:12
  • Have you tried passing them as array? context.Database.ExecuteSqlCommand("delete MasterSmsCampaignCertificateInfo where MasterSmsCampaignGuid = @p0 and CertificateId = @p1", new object[]{ TheCampaignGuid, certInfo.CertificateId}); – Oscar Jun 20 '12 at 15:31
  • It's a params parameter, so it should not matter, and yes, I've tried that too :) – SWeko Jun 20 '12 at 15:33
  • 1
    This syntax works fine, the problem was in the generated query. – SWeko Jun 21 '12 at 06:57
2
var sql = @"Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);
Gho5t
  • 1,060
  • 1
  • 12
  • 23
  • This syntax works fine, the problem was with my query format. – SWeko Jun 21 '12 at 06:56
  • This is just a copy of another answer from a similar question, without adjusting for context. http://stackoverflow.com/a/5475210/642054 – Ryan Kyle Feb 17 '15 at 22:15