存储过程返回整数，而不是ISingleResult [英] Stored procedure returning integer instead of ISingleResult

问题描述

ALTER PROCEDURE [dbo].[VCT_SP_SUBJECT_EMAIL_REMINDER]
AS
CREATE TABLE #tempEstimatedDates(
SUBJECT_ID int,
FIRST_NAME varchar(200),
LAST_NAME varchar(200),
MIDDLE_NAME varchar(200),
EMAIL_ID varchar(200),
ACTIVITY_VALUE datetime,
SUB_VISIT_ID int,
VISIT_NO INT,
VISIT_DETAIL_ID INT,
PROTOCOL_ID INT,
LOCATION_ID INT,
SITE_ID INT,
SITE_NAME VARCHAR(200),
PROTOCOL_NAME VARCHAR(200)  
)
INSERT INTO   

#tempEstimatedDates(SUBJECT_ID,FIRST_NAME,LAST_NAME,MIDDLE_NAME,EMAIL_ID,      
ACTIVITY_VALUE,
SUB_VISIT_ID,VISIT_NO,VISIT_DETAIL_ID,PROTOCOL_ID       
 ,LOCATION_ID,PROTOCOL_NAME,SITE_ID,SITE_NAME) 
SELECT * FROM dbo.[GET_SUBJECT_SCHEDULE]('Estimated Date Of Next Visit')

---TABLE FOR ACTUAL DATE OF VISIT---
CREATE TABLE #tempActualDates(
SUBJECT_ID int,
FIRST_NAME varchar(200),
LAST_NAME varchar(200),
MIDDLE_NAME varchar(200),
EMAIL_ID varchar(200),
ACTIVITY_VALUE datetime,
SUB_VISIT_ID int,
VISIT_NO INT,
VISIT_DETAIL_ID INT,
PROTOCOL_ID INT,
LOCATION_ID INT,
SITE_ID INT,
SITE_NAME VARCHAR(200),
PROTOCOL_NAME VARCHAR(200)  
)
INSERT INTO 

 #tempActualDates(SUBJECT_ID,FIRST_NAME,LAST_NAME,MIDDLE_NAME,EMAIL_ID,
 ACTIVITY_VALUE,SUB_VISIT_ID,
VISIT_NO,VISIT_DETAIL_ID,PROTOCOL_ID ,LOCATION_ID,PROTOCOL_NAME,SITE_ID,SITE_NAME) 
SELECT * from dbo.[GET_SUBJECT_SCHEDULE]('Actual Date Of Visit')
select * from #tempEstimatedDates 
where  subject_id in 
(select subject_id from #tempActualDates) and visit_no not in (select visit_NO from   
#tempActualDates)
AND 
(   convert(varchar,ACTIVITY_VALUE,101)=convert(varchar,getdate(),101)
or dateadd(d,2,convert(varchar,getdate()  
,101))=convert(varchar,ACTIVITY_VALUE,101)  
or dateadd(d,1,convert(varchar,getdate() ,101))=convert(varchar,ACTIVITY_VALUE,101)
)
UNION
select * from #tempEstimatedDates
where  subject_id NOT in 
(select subject_id from #tempActualDates)
AND 
(   convert(varchar,ACTIVITY_VALUE,101)=convert(varchar,getdate(),101)
or dateadd(d,2,convert(varchar,getdate()  
,101))=convert(varchar,ACTIVITY_VALUE,101)  
or dateadd(d,1,convert(varchar,getdate() ,101))=convert(varchar,ACTIVITY_VALUE,101)
)
select * from #tempEstimatedDates 
where  subject_id in 
(select subject_id from #tempActualDates) and visit_no not in (select visit_NO from   
#tempActualDates)
AND 
(   --convert(varchar,ACTIVITY_VALUE,101)=convert(varchar,getdate(),101) or 
DATEDIFF(d,convert(varchar,ACTIVITY_VALUE,101),convert(varchar,getdate()  
,101))=2    
or DATEDIFF(d,convert(varchar,ACTIVITY_VALUE,101),convert(varchar,getdate()  
,101))=1    
)
UNION
select * from #tempEstimatedDates
where  subject_id NOT in 
(select subject_id from #tempActualDates)
AND 
(   --convert(varchar,ACTIVITY_VALUE,101)=convert(varchar,getdate(),101) OR
DATEDIFF(d,convert(varchar,ACTIVITY_VALUE,101),convert(varchar,getdate()  
 ,101))=2   
or DATEDIFF(d,convert(varchar,ACTIVITY_VALUE,101),convert(varchar,getdate() 
,101))=1    
)
drop table #tempEstimatedDates
drop table #tempActualDates

这是我的存储过程返回SQL Server中的数据的两个表。但是，当我拖动和DBML它的返回类型放弃这一SP成为INT而不是Isingleresult。如何解决这个问题。

This is my stored procedure which returns two tables of data in sql server. But when i drag and drop this SP in dbml its return type becomes int instead of Isingleresult. how to solve this problem.

推荐答案

我有这个问题，以及这肯定是由临时表所致。我固定它的方法是添加

I've had this problem as well which was definitely caused by temp tables. The way I fixed it was to add

SET FMTONLY OFF;

存储过程的开始。该解决方案是从这个博客其中谈到实体框架，但同样的似乎适用于LINQ到SQL。

to the start of the stored procedure. The solution is taken from this blog which talks about Entity Framework but the same seems to apply to Linq to Sql.

这篇关于存储过程返回整数，而不是ISingleResult的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！