13

Here's my SQL Server stored procedure :

ALTER PROCEDURE [dbo].[SearchUser]
  (@Text NVARCHAR(100),  
   @TotalRows INT = 0 OUTPUT)   
AS
BEGIN 
   SELECT @TotalRows=1000
   SELECT * from Users
END

And my C# code

using (var context = new TestDBEntities())
{
    var outputParameter = new ObjectParameter("TotalRows", typeof(Int32));
    context.SearchUser("", outputParameter);
    Response.Write(outputParameter.Value);
}

However outputParameter.Value always is null.

Could anybody tell me why?

Jacob Phan
  • 732
  • 2
  • 6
  • 20

3 Answers3

44

Output parameters filled by its actual values during the execution of the stored procedure.

But table-valued stored procedure actually get executed only in moment when you're trying to iterate resulting recordset, but not calling a wrapper method.

So, this DOES'T work:

using (var context = new TestDBEntities()) 
{ 
    var outputParameter = new ObjectParameter("TotalRows", typeof(Int32)); 
    context.SearchUser("", outputParameter); 

    // Paremeter value is null, because the stored procedure haven't been executed
    Response.Write(outputParameter.Value); 

}

This DOES:

using (var context = new TestDBEntities()) 
{ 
    var outputParameter = new ObjectParameter("TotalRows", typeof(Int32)); 

    // Procedure does not executes here, we just receive a reference to the output parameter
    var results = context.SearchUser("", outputParameter);

    // Forcing procedure execution
    results.ToList();

    // Parameter has it's actual value
    Response.Write(outputParameter.Value); 

}

When you're working with stored procedures what don't return any recordset, they execute immediately after a method call, so you have actual value in output parameter.

sparkyShorts
  • 630
  • 9
  • 28
Sergey Kostrukov
  • 1,143
  • 15
  • 24
  • Thanks, this helped me. It seems we have to put the results into a list BEFORE reading the output param's value – Netricity Mar 20 '12 at 12:27
  • Really appreciate your help, you save me hours of searching – Tarek El-Mallah Oct 09 '14 at 20:23
  • What if you have an int return type instead of a list? – Alao Apr 08 '15 at 19:08
  • I believe you cannot have int as a return type of a table-valued stored proc. If you return something by just `SELECT 1` for example, then you'll get a List with only one element. – Sergey Kostrukov Apr 08 '15 at 19:52
  • And of course, one could consolidate this to just: context.SearchUser("", outputParameter).ToList(); – LarryBud Mar 02 '18 at 03:40
  • If the function mapping in EF properties show a return type of 'none' then the .ToList() is unnecessary. It seems that if the stored procedure has some form of defined return type then execution is deferred and outbound parameters remain null until execution. Good find by the way! – barrypicker Mar 07 '18 at 23:10
1

We had a simular issue due to defered excecution our unit tests failed. In short if you have a stored proc that does NOT return anything you need to be sure to set the response type as 'None' when set as 'None' it will be excecuted when called and not defered.

In case you return anything (E.g. Scalar type of String results) it will excecute it when you use the result even if that .Count() or .ToList() is outside of the method that contains the function call.

So try not to force excecution if not need, when needed it should excecute but be sure to declare it correctly or it might not work.

Marius Vorster
  • 196
  • 1
  • 10
  • Set response type as 'none'? Where? Code? – James Bailey Aug 20 '15 at 18:27
  • @JamesBailey - the entity framework designer has a model browser, and associated properties sheet. In this property sheet you can set the import function return type to none for your stored procedure entity framework function import. Of course this assumes database first approach. I am not sure how to do this setting if using code first... – barrypicker Mar 07 '18 at 23:11
0

I have same problem before. The main reason I think that the entities framework has the bug in case the user stored procedure has output parameter and return a result set. For example:

ALTER PROCEDURE [dbo].[SearchTest]
(   
    @RowTotal   INT = 0 OUTPUT,
    @RowCount   INT = 0 OUTPUT
)
AS
BEGIN   
    SET NOCOUNT ON
    SELECT * FROM SomeThing 
    SELECT @RowTotal = 1233, @RowCount = 5343
END

However if you change the user stored procedure as following, you can get the output params

ALTER PROCEDURE [dbo].[SearchTest]
(   
    @RowTotal   INT = 0 OUTPUT,
    @RowCount   INT = 0 OUTPUT
)
AS
BEGIN   
    SET NOCOUNT ON  
    SELECT @RowTotal = 1233, @RowCount = 5343
END

You can workaround as following:

ALTER PROCEDURE [dbo].[SearchTest]
AS
BEGIN   
    DECLARE @RowTotal INT, @RowCount INT

    SET NOCOUNT ON  
    SELECT @RowTotal = 1233, @RowCount = 5343

    SELECT @RowTotal  AS RowTotal, @RowCount AS RowCount, s.*
    FROM SomeThing s

END

If anybody has better solution, please tell me

user710883
  • 1
  • 1