0

I have written a stored procedure that will return me ID of a row if found and if not found, will create a row and return ID of the newly created row. I am facing problems in retrieving this returned value in my C# function.

The Stored Procedure:

PROCEDURE [TagDatabase].[GetTagID] 
    -- Add the parameters for the stored procedure here
    @TagName as varchar(200), 
    @UserID int = 0,
    @TagID int = 0 OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    select @TagID=ID from Tag where Name = @TagName and UserID=@UserID;
    IF NULLIF(@TagID, '') IS NULL
    BEGIN
        DECLARE @OutputTbl TABLE (ID INT)

        INSERT INTO Tag(Name, ParentID, UserID)
        OUTPUT INSERTED.ID INTO @OutputTbl(ID)
        VALUES (@TagName, 0, @UserID);

        select @TagID=ID from @OutputTbl


    END

    select @TagID
END

My function to retrieve the value returned by this:

ObjectParameter objParam = new ObjectParameter("TagID", typeof(long));

context.GetTagID("newTag", 10, objParam);
context.SaveChanges();
long id = Convert.ToInt64(Convert.ToInt64(id)));
labyrinth
  • 1,104
  • 3
  • 11
  • 32

1 Answers1

0

Change

long id = Convert.ToInt64(Convert.ToInt64(id)));

to

long id = Convert.ToInt64(objParam.Value);

Mike
  • 3,186
  • 3
  • 26
  • 32
  • The expression suggested by you always returns 0. Whereas, if I run the same procedure on the SQL server management studio, it works perfectly fine. Infact, objParam.Value is always received as null. – labyrinth Jun 04 '15 at 17:56
  • I'm not sure why this wouldn't work, do you leave that final select @TagID in the code? That returns a resultset, but you should still be able to get the output parameter. The only other thing I can think of would be to change the typeof(long) to typeof(int) to match your stored proc parameter. – Mike Jun 04 '15 at 18:19
  • Similar thread http://stackoverflow.com/questions/10339750/entity-framework-stored-procedure-return-value?rq=1 – Mike Jun 04 '15 at 18:22