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