none
Stored procedure return values RRS feed

  • Question

  • I created a function import for a insert stored procedure I have in my database.

    The insert sproc returns an integer and the parameter in the stored procedure is marked with OUTPUT.

    When I attempt to add a function import for the sproc and select "get column information", nothing appears. How can I get back the int value I am expecting from the stored procedure?

    Monday, September 24, 2012 11:21 PM

Answers

  • Hi velocetti,

    When you import the stored procedure as function, please set the return type as 'None'. I wrote a demo to show how to retrieve output parameter, please refer to it.

    stored procedure:

    CREATE PROCEDURE dbo.TestInsert   
    @Name varchar(50),   
    @Description varchar(max),     
    @TestID int OUT   
    AS   
    INSERT INTO Test(Name,Description) VALUES(@Name,@Description)   
    SET @TestID = SCOPE_IDENTITY()

    Code:

    static void Main(string[] args)
            {
                using (TestEntities context = new TestEntities())
                {
                    ObjectParameter output = new ObjectParameter("TestID", typeof(Int32));
                    context.TestInsert("allen", "123",output);
                    Console.Write(output.Value);
                    Console.Read();
                }
            }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Wednesday, September 26, 2012 3:26 AM
    Moderator

All replies

  • Hi velocetti,

    Welcome to MSDN Forum.

    Working with stored procedure which contains output parameter in EF, please set the return type as 'None'.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Tuesday, September 25, 2012 1:28 AM
    Moderator
  • Right I understand that, but I need to return back the new identity that was created when I insert a value. I have an output parameter setup in my sproc but how can i return that?
    Tuesday, September 25, 2012 3:05 PM
  • Hi velocetti,

    When you import the stored procedure as function, please set the return type as 'None'. I wrote a demo to show how to retrieve output parameter, please refer to it.

    stored procedure:

    CREATE PROCEDURE dbo.TestInsert   
    @Name varchar(50),   
    @Description varchar(max),     
    @TestID int OUT   
    AS   
    INSERT INTO Test(Name,Description) VALUES(@Name,@Description)   
    SET @TestID = SCOPE_IDENTITY()

    Code:

    static void Main(string[] args)
            {
                using (TestEntities context = new TestEntities())
                {
                    ObjectParameter output = new ObjectParameter("TestID", typeof(Int32));
                    context.TestInsert("allen", "123",output);
                    Console.Write(output.Value);
                    Console.Read();
                }
            }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Wednesday, September 26, 2012 3:26 AM
    Moderator