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.
Code:
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);
SP:
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 CommandType
property).
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.