CODEWITHSUNDEEP
c#asp.netstored-procedures
jin cheng teo
jin cheng teo

Reputation: 305

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

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?

Upvotes: 2

Views: 893

Answers (2)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

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

Upvotes: 1

jmcilhinney
jmcilhinney

Reputation: 54427

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")

Upvotes: 2

Related Questions