ASP.NET MVC calling stored procedure with error parameter not supplied

Question

I currently have an ASP.NET MVC application trying to execute a stored procedure.

My stored procedure is like this:

ALTER PROCEDURE [dbo].[_SelectFromQry] 
    @queryname NVARCHAR(255)
AS
BEGIN
    SELECT TBLNAME, TBLCOL, TBLCOLLABEL, POSITION
    FROM QRY
    WHERE QUERYNAME = @queryname
END

Inside my controller, I have written a code like this:

var result =  db.Database.SqlQuery<RESULT_FROM_QRY>("_SelectFromQry", new   SqlParameter("queryname","INVAVAIL")).ToList();

When the application reaches this line, I get a

SqlCilent.SqlExcepction: procedure '_SelectFromQry' expects parameter '@queryname' which was not supplied.

I am not sure if I am calling the stored procedure correctly with my code?

score 2 · Answer 1 · answered May 22 '18 at 02:07

2

Presumably the issue is that you use "queryname" rather than "@queryname" in the SqlParameter constructor, i.e. this:

new SqlParameter("queryname","INVAVAIL")

should be this:

new SqlParameter("@queryname","INVAVAIL")

answered May 22 '18 at 02:07

jmcilhinney

50,448
5
26
46

I modified accordingly to your way and still get the same exception... ... – jin cheng teo May 22 '18 at 02:16
In that case, I can only assume that there is an issue inside your `SqlQuery` method but you haven't shown us that code so I can only guess. – jmcilhinney May 22 '18 at 02:30

Tetsuya Yamamoto · Accepted Answer · 2018-05-22T02:36:28.630

Possibly you need to include possible parameter name(s) inside query string like this:

var result = db.Database.SqlQuery<RESULT_FROM_QRY>("_SelectFromQry @queryname", 
             new SqlParameter("queryname", "INVAVAIL")).ToList();

Or optionally use EXEC prefix:

var result = db.Database.SqlQuery<RESULT_FROM_QRY>("EXEC _SelectFromQry @queryname", 
             new SqlParameter("queryname", "INVAVAIL")).ToList();

Since your stored procedure declares an input parameter without specifying default value of NULL (i.e. using required parameter instead of optional parameter), the parameter name must be supplied in order to execute it.

Reference:

SQL Server stored procedure parameters

ASP.NET MVC calling stored procedure with error parameter not supplied

2 Answers2