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

最新推荐文章于 2024-11-15 18:38:47 发布

weixin_30563319

最新推荐文章于 2024-11-15 18:38:47 发布

阅读量118

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/xiaochun126/p/4691529.html

版权

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

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

3、解决方案

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

Ex:

DECLARE @TempTable TABLE

(

AttributeID INT,

Value NVARCHAR(200)

)

INSERT INTO @TempTable Select * from Attribute

--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