The return types for the following stored procedures could not be detected

1、使用dbml映射数据库，添加存储过程到dbml文件时报错。

2、原因：存储过程中使用了临时表

3、解决方案

3.1 通过自定义表值变量实现

Ex:

DECLARE @TempTable TABLE

(

AttributeID INT,

Value NVARCHAR(200)

)

INSERT INTO @TempTable Select * from Attribute

OR

--Execute SP and insert results into @TempTable

INSERT INTO @TempTable Exec GetAttribute @Id

You can do all operation which you was doing with #Temp table like Join, Insert, Select etc.

3.2  选中Db.dmbl文件--右键--新建--class文件--名称Db.cs，自定义partial class Db，写获取数据的方法，其中MyModel为你需要返回的数据model，Id为存储过程输入参数，存储过程名称为GetDataById（原名为[GetProjectsByClientId]）

 

 public partial class Db {

 [global::System.Data.Linq.Mapping.FunctionAttribute(Name = "dbo.GetDataById")]
    public ISingleResult<MyModel> GetProjectsByClientId([global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "NVarChar(10)")] string Id)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((System.Reflection.MethodInfo)(System.Reflection.MethodInfo.GetCurrentMethod())), Id);
        return ((ISingleResult<MyModel>)(result.ReturnValue));
    }

}

 

调用： IList<MyModel> lst = db.GetDataById(id).ToList();

4、存储过程（进行了简化，理解意思即可）

IF object_id('GetDataById') IS NOT NULL
DROP PROCEDURE [dbo].[GetDataById]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[GetDataById]
 @clientId nvarchar(10)
 as
 begin
  SET NOCOUNT ON;
  IF object_id('tempdb..##tempProject') IS NOT NULL
        DROP TABLE ##tempProject
    
  select * into ##tempProject from Project where ClientId=@ClientId
  select p.id as ID,p.Name,a.Code,b.dtDate
           from ##tempProject p
        left join [dbo].[A] a on p.Id=a.ProjectId
        left join [dbo].[B] b on b.ProjectId=a.ProjectId
        
 end
GO

参考：

http://stackoverflow.com/questions/7035669/the-return-types-for-the-following-stored-procedures-could-not-be-detected

http://riteshkk2000.blogspot.com.au/2010/08/error-unknown-return-type-return-types.html

http://beyondrelational.com/modules/2/blogs/45/posts/12025/how-to-get-multiple-result-set-of-procedure-using-linq-to-sql.aspx