The parameterized query expects the parameter p1 which was not supplied

.net-4.5 asp.net-web-api c# entity-framework-6 sql-server-2012

Question

I have a stored proc as below:

CREATE PROCEDURE [dbo].[MyProc] 
    @p1 as int,
    @p2 as smalldatetime,
    @p3 as int,
    @p4 as varchar(255),
    @p5 as int = null,
    @p6 as numeric(18,2) = 0,
    @p7 as char(2) = null
AS

...

When I execute the below I get results:

EXEC dbo.MyProc
    @p1 = 0,
    @p2 = '5/29/2015',
    @p3 = NULL,
    @p4 = NULL,
    @p5 = 233,
    @p6 = 0,
    @p7 = NULL

But when I use Entity Framework's Database.SqlQuery, I get the The parameterized query '(@p1 bigint @p2 datetime @p3 nvarchar(4' expects the parameter '@p1' which was not supplied. Below is the code I used.

using (var context = new DbContext())
{   
    context.Database.ExecuteSqlCommand(
        @"EXEC dbo.MyProc @p1, @p2, @p3, @p4, @p5, @p6, @p7",
        new SqlParameter("p1", 0),
        new SqlParameter("p2", myDate), //myDate has value
        new SqlParameter("p3", DBNull.Value),
        new SqlParameter("p4", DBNull.Value),
        new SqlParameter("p5", myValue),//myValue has value
        new SqlParameter("p6", 0),
        new SqlParameter("p7", string.Empty));//I tried this with DBNull.Value; but no difference
}

Can anyone help?

1
2
1/12/2016 12:06:31 AM

Accepted Answer

"For some reason when I pass 0, it is converted to BigInt. I do not know why. I parsed 0 to int and it worked.

using (var context = new DbContext())
{   
    context.Database.ExecuteSqlCommand(
        @"EXEC dbo.MyProc @p1, @p2, @p3, @p4, @p5, @p6, @p7",
        new SqlParameter("p1", int.Parse("0"),
        new SqlParameter("p2", myDate),
        new SqlParameter("p3", DBNull.Value),
        new SqlParameter("p4", DBNull.Value),
        new SqlParameter("p5", myValue),
        new SqlParameter("p6", int.Parse("0")),
        new SqlParameter("p7", DBNull.Value));
}
6
1/12/2016 12:25:40 AM

Popular Answer

You need to include the @ character in the parameter names.

new SqlParameter("@p1", 0)


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow