Problem with an iView based on Stored Procedure

[Former Member]

Hello,

I've created an iView based on a Stored Procedure.

While the procedure returns the correct values when I test it under Query Analyser, the iView returns an empty table. Here is the SP (It is a bit long):

CREATE PROCEDURE ProcRequestCountSapakim @StartDate varchar (10), @EndDate varchar (10)  AS

begin 
 
DECLARE @ReqCount as int,@ReqCountCheck as int,@ReqCountRead as int,@ReqCountRequest as int,@m_StartDate as datetime, @m_EndDate  as datetime
DECLARE @ZAHYAN_ID int , @x smallint, @y smallint
set @m_StartDate = convert (datetime ,@StartDate,103)
set @m_EndDate = convert (datetime ,@EndDate,103)
 
CREATE TABLE [#Tbl_Sapkim] (
 num int identity,
 [ID] [numeric](10, 0) NOT NULL ,
 [SAPAK] [varchar] (100) COLLATE Hebrew_CI_AS NULL ,
 ReqCount int,
 ReqCountRead INT,
        ReqCountRequest INT,
        ReqCountCheck INT
 
) ON [PRIMARY]
--GO
 
insert into #Tbl_Sapkim ([ID],[SAPAK])
select [ID],[SAPAK]
from Tbl_Sapkim
 
SET @X = 1
set @y = (SELECT count(num)  FROM #Tbl_Sapkim)
WHILE @X <= @y
 
begin
set @ZAHYAN_ID = (select [ID] from #Tbl_Sapkim where num = @x)
 
 
 
SET @ReqCount = (SELECT count(*) 
FROM dbo.REQUEST 
WHERE ((ORIGIN = 'L')OR(ORIGIN = 'I')OR
                     (STATUS_NBR = 0))and dbo.REQUEST.ZAHYAN_ID = @ZAHYAN_ID AND RESPONSE_TIME BETWEEN @m_StartDate AND dATEADD(d, 1, @m_EndDate))

SET @ReqCountRead = (SELECT count(*)FROM dbo.REQUEST WHERE ((ORIGIN = 'I')  AND (HEADER_ONLY =0)) 
   and dbo.REQUEST.ZAHYAN_ID = @ZAHYAN_ID and RESPONSE_TIME BETWEEN @m_StartDate AND dATEADD(d, 1, @m_EndDate))
 

SET @ReqCountRequest = (SELECT  count (*)FROM dbo.REQUEST WHERE ((ORIGIN = 'L')AND(SACH_ALUT>=59)AND(HEADER_ONLY =0)) 
   and   dbo.REQUEST.ZAHYAN_ID = @ZAHYAN_ID and RESPONSE_TIME BETWEEN @m_StartDate AND dATEADD(d, 1, @m_EndDate))
 

SET @ReqCountCheck = (SELECT count (*)FROM dbo.REQUEST WHERE ((ORIGIN = 'I') AND (HEADER_ONLY =1)) 
   and  dbo.REQUEST.ZAHYAN_ID = @ZAHYAN_ID and RESPONSE_TIME BETWEEN @m_StartDate AND dATEADD(d, 1, @m_EndDate))
 

UPDATE  #Tbl_Sapkim 
SET ReqCount = @ReqCount,
 ReqCountRead = @ReqCountRead,
        ReqCountRequest = @ReqCountRequest,
        ReqCountCheck = @ReqCountCheck
where  #Tbl_Sapkim.[ID]= @ZAHYAN_ID
 
 
 
SET @X = @x+1
 
end
 
select SAPAK , ReqCount,ReqCountRead,ReqCountRequest,ReqCountCheck
from #Tbl_Sapkim
 
end
GO

[Former Member]

HI mate,

I am having the same problem now, were you able to resolve this?

Dushy

[Former Member]

Anyone...?