I have been trying to understand why I am getting an exception when trying to use a stroed procedure for the purpose of inserting a record. I have a company table in the database with the columns Id(Guid), Name(nvarchar) and timestamp(datetime) .The Id column is a RowGuid. Also I am using SQL server 2005 and EF4
The stored procedure is
ALTER PROCEDURE [dbo].[InsertCompanySP]
@companyName NVarChar(50),
@timeStamp datetime
AS
BEGIN
SET NOCOUNT ON;
Insert into dbo.Company(Name,TimeStamp) values (@companyName, @timeStamp)
select SCOPE_IDENTITY() as Id
END
the code I am using to call this stored procedure is
using (var context = new testEntities())
{
try
{
Company c = new Company();
c.Name = "abcd";
c.TimeStamp = DateTime.Now;
context.Companies.AddObject(c);
context.SaveChanges();
Console.WriteLine(c.Id);
}
catch (Exception ex)
{
Console.WriteLine("Exception Occurred");
}
}
as you can guess, In the Entity Framework model's Insert stored procedure mapping the companyName and timeStamp are bound to the Name and TimeStamp properties of my Company Entity and the Id is bound to the Id property of the Company Entity. If I go ahead and remove the result column binding , everything works fine (the stored procedure is called and no exception is thrown). When I have the result binding in place to return the Id of the added Company, I can still see the exact same stored procedure being called but it throws an exception saying
"A null store-generated value was returned for a non-nullable member 'Id' of type 'testModel.Company'."
I am assuming I might have something incorrect in my stored procedure with returning the Id of the inserted row but I am not able to figure it out. I also tried using the following in the stored procedure for returning the value of the Company Id inserted but I still get the same exception
select Id = @@IDENTITY
UPDATE:I forgot to mention that the Id is also marked with StoreGeneratedPattern = Identity in the EF
UPDATE: I solved the problem thanks to @olivehour.. modified my stored procedure to
ALTER PROCEDURE [dbo].[InsertCompanySP]
@companyName NVarChar(50),
@timeStamp datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TmpTable TABLE (Id uniqueidentifier);
Insert into dbo.Company(Name,TimeStamp)
OUTPUT INSERTED.Id INTO @TmpTable
values (@companyName, @timeStamp)
select Id from @TmpTable
END
