I am trying to read the OUT parameters value returned by stored procedure but it throws error DBNULL. Even the procedure is returning the value.
int? outParam= 0; SqlParameter sqlParam= new SqlParameter("@model", user.Name+" Model"); SqlParameter sqlParamOut = new SqlParameter("out", outParam); sqlParamOut.DbType = System.Data.DbType.Int32; sqlParamOut.Direction = System.Data.ParameterDirection.Output; UsersPhonesDBContext.Database.ExecuteSqlCommand("dbo.InsertPhones @model, @out", sqlParam, sqlParamOut); outParam = Convert.ToInt32(sqlParamOut.Value);
alter PROCEDURE InsertPhones ( @model varchar(100), @out int output ) AS BEGIN Insert into Phones (Model, Manufacturer) Values ( @model, 'Hunain Phone' ) set @out= SCOPE_IDENTITY(); Select @out END GO
out parameters in SQL Server are fun. You have to repeat yourself everywhere.
Specifically, here, you've forgotten to mention it in the parameter list when calling the stored proc.
dbo.InsertPhones @model, @out should be
dbo.InsertPhones @model, @out out. There may also be a way to omit the parameter list from this string and instruct SQL Server that you're giving it a stored procedure name, not arbitrary query text, but I'm not sure how you would do that specifically here (with an
SqlCommand object, it would be via the
If you're using an
out parameter, the
select at the end of the stored procedure is unnecessary, but I assume that was an attempt at a fix.