The unhandled system.void return exception raised when the associated stored procedure method was run in LINQ-to-SQL

Question

I have a SQL Server 2008 stored procedure (pseudo code below) that looks like this:

CREATE PROCEDURE MyProc AS BEGIN CREATE DummyTable INSERT INTO DummyTable SELECT xxx INSERT INTO DummyTable SELECT yyy IF EXISTS FinalTable DROP FinalTable EXEC sp_RENAME 'DummyTable', 'FinalTable' END GO 

Note that there is no return type / value for this stored process. I have this stored proc mapped in my LINQ-TO-SQL designer (ASP.NET MVC2 application). However, when I call the method as follows:

 using (MyContext context = new MyContext()) { context.MyProc(); } 

However, in my logs, I found that this exception is thrown when this method runs:

System.InvalidOperationException: "System.Void" is not a valid return type for the displayed stored procedure method. in System.Data.Linq.SqlClient.QueryConverter.TranslateStoredProcedureCall (MethodCallExpression mce, MetaFunction function) with System.Data.Linq.SqlClient.QueryConverter.VisitMappedFunctionCall (MethodCallExpression mc) with System.Data.LinqCallIt.ConclientClientClientClientClientClientClientClientClientClientClientClientClientClientClientClientClientClientCall ) when System.Data.Linq.SqlClient.QueryConverter.VisitInner (Expression node) in System.Data.Linq.SqlClient.QueryConverter.ConvertOuter (Expression node) in System.Data.Linq.SqlClient.SqlProvider.BuildQuery (Expression query, annotations SqlNodeAnnotations) in System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute (Expression query) in System.Data.Linq.DataContext.ExecuteMethodCall (Instance object, methodInfoInfo, Object [])

Can someone help me determine why this exception is thrown?

Thanks A.

UPDATED: Automatically generated code in the L2S constructor file:

 [global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.MyProc")] public void MyProc() { this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); } 

Answer 1

Guys, to let you know that I set nocount on and that sorted the problem!

I had to re-add my sp in design mode.

Answer 2

What did the generated linq-to-sql code look like? Perhaps you manually changed it to return void?

Creating a stored procedure that does not produce output like this:

 CREATE PROCEDURE MyProc AS BEGIN IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DropMe') DROP TABLE DropMe END GO 

should generate the following code when adding it to the l2s design surface -

 [Function(Name = "dbo.MyProc")] public int MyProc() { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); return ((int)(result.ReturnValue)); } 

If you return void instead, it will cause it to throw this exception.

So, if I change the above to this -

 [Function(Name = "dbo.MyProc")] public void MyProc() { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); return; } 

He will throw it on a call -

Line 17: public void MyProc () Line 18: {Line 19: IExecuteResult result = this.ExecuteMethodCall (this, ((MethodInfo) (MethodInfo.GetCurrentMethod ()))); Line 20: return; // ((INT) (result.ReturnValue)); Line 21:}

Source file: C: \ Build \ blah.cs Line: 19

Stack trace:

[InvalidOperationException: "System.Void" is not a valid return type for the displayed stored procedure method.]
System.Data.Linq.SqlClient.QueryConverter.TranslateStoredProcedureCall (MethodCallExpression mce, MetaFunction function) +904265