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?

jmcilhinney · Accepted Answer · 2018-05-22 02:07:14Z

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, 2018 at 2:07

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

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

2 Comments

jin cheng teo Over a year ago

I modified accordingly to your way and still get the same exception... ...

jmcilhinney Over a year ago

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.

Tetsuya Yamamoto · Accepted Answer · 2018-05-22 02:36:28Z

1

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

edited May 22, 2018 at 2:36

answered May 22, 2018 at 2:26

Tetsuya Yamamoto

25.1k8 gold badges43 silver badges63 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related