LINQ TO SQL figures out what a method returns by executing it with SET FMTONLY ON, which causes SQL Server to not really run the method but instead just examine the tables and columns used.

Unfortunately, this completely doesn’t work for dynamic SQL, causing the LINQ designer to not be able to figure out the return type.  It even goes so far as to grays out the option for you to set the return type, forcing it to (none).

You can manually hack on the .cs file, but that file gets regenerated so it should be avoided.  Instead, if you just have a ‘get’ style sproc that doesn’t have bad side-effects, you can tell SQL Server that it’s ok to really run your sproc.

First, verify you have no bad side effects from running your sproc (e.g. that it’s ok to call it whenever Visual Studio thinks it wants to). 

Then, inside of the stored procedure, add:

SET FMTONLY OFF ;

Next, make sure your method runs ok with all null parameters. I do this by providing some reasonable values:

IF (@Param1 IS NULL)
BEGIN
  SET @Param1=0;
END

Finally, run it from Visual Studio to make sure it works:

SET FMTONLY ON;
Exec MySproc null, null, null
SET FMTONLY OFF;

If you get back columns, you’re great.  If not, check that your reasonable values work ok.

Finally, the LINQ designer does some aggressive caching of method return types. To change a return type, I have had to delete the method, save my project, close the connection in server explorer, exit visual studio, re-open the connection, and re-drag the method over for it to get over the (none) return type and let me pick one.  “Refresh” didn’t work.

Note that return type will stay as (none) in Visual Studio if it encounters a problem running your method, so be sure it works with SET FMTONLY ON; Exec [methodname] [null parameters] before trying to fight the cache problem.

DamienG's blog was the best source of info I found while troubleshooting this problem.