SQL查询输出

Question

我试试这个sql查询

Select 
    tblRV.ID as ID, tblRV.Owner, tblRV.Regno, 
    (Select Count(*) as total 
    from tblvv 
    where MID = tblRV.ID and Name <> '') as total, 
    tblRV.MA, tblRV.MS 
from 
    tblReg 
inner join 
    tblRV On tblReg.RID = tblRV.RID 
where 
    tblReg.StartDate >= '2016-06-01 00:00:00' and 
    tblReg.EndDate <= '2016-06-08 23:59:59' and 
    tblReg.Region = 'UK' 
order by 
    tblRV.Owner 

该查询返回24行。所以，当我建立了我的程序，这样

grid_data 'UK' ,'2016-06-01 00:00:00', '2016-06-08 23:59:59' 

存储过程执行：

ALTER procedure [dbo].[grid_data] 
    @region varchar(50), 
    @fromdate datetime, 
    @todate datetime 
AS 
    SELECT 
     tblRV.ID as ID, tblRV.Owner, tblRV.Regno, 
     (SELECT COUNT(*) AS total 
     FROM tblvv 
     WHERE MID = tblRV.ID and Name <> '') AS total, 
     tblRV.MA, tblRV.MS 
    FROM 
     tblReg 
    INNER JOIN 
     tblRV ON tblReg.RID = tblRV.RID 
    ORDER BY 
     tblRV.Owner 

此过程返回24913分的记录。

Answer 1

尝试修改您的SP，包括根据您的参数WHERE条款 - 就像这样：

ALTER procedure [dbo].[grid_data] 
@region varchar(50), 
@fromdate datetime, 
@todate datetime 
as 
Select tblRV.ID as ID, tblRV.Owner, tblRV.Regno, 
     (Select Count(*) as total from tblvv WHERE MID = tblRV.ID and Name <> '') 
       as total,tblRV.MA, tblRV.MS 
from tblReg inner join tblRV On tblReg.RID = tblRV.RID 
WHERE tblReg.StartDate >= @fromdate AND tblReg.EndDate <= @todate 
and tblReg.Region = @region 
order by tblRV.Owner 

Answer 2

在存储过程中