create PROC Find(@Date1 varchar(10), @Date2 varchar(10), @Num int Output, @PageCout int = 1,
@PageSize int = 20)
AS
DECLARE @Shen int
DECLARE @Sql varchar(1000)
BEGIN
SELECT @Num = COUNT(*)
FROM saleinfo
WHERE datediff(dd,@Date1,DATEAGREED)>= 0 AND datediff(dd,DATEAGREED,@Date2)>= 0
print cast(@Num as varchar(10)) + 'row'
set @Shen=@Num-(@PageCout-1)*@PageSize
print cast(@Shen as varchar(10)) + 'row'
set @Sql='select top '+convert(varchar(10),@PageSize)+' OWNERIDNO,icount,sfmj,sfje'+' from (select top '+convert(varchar(10),@Shen)+' OWNERIDNO,Count(*) as icount,sum(HOUSEAREA) as sfmj'+',sum(TOTAMOUNT) as sfje from saleinfo where datediff(dd,'''+@Date1+''',DATEAGREED)>= 0 AND datediff(dd,DATEAGREED,'''+@Date2+''')>=0 '
set @Sql=@Sql+' group by OWNERIDNO ) a'
exec(@Sql)
end
@PageSize int = 20)
AS
DECLARE @Shen int
DECLARE @Sql varchar(1000)
BEGIN
SELECT @Num = COUNT(*)
FROM saleinfo
WHERE datediff(dd,@Date1,DATEAGREED)>= 0 AND datediff(dd,DATEAGREED,@Date2)>= 0
print cast(@Num as varchar(10)) + 'row'
set @Shen=@Num-(@PageCout-1)*@PageSize
print cast(@Shen as varchar(10)) + 'row'
set @Sql='select top '+convert(varchar(10),@PageSize)+' OWNERIDNO,icount,sfmj,sfje'+' from (select top '+convert(varchar(10),@Shen)+' OWNERIDNO,Count(*) as icount,sum(HOUSEAREA) as sfmj'+',sum(TOTAMOUNT) as sfje from saleinfo where datediff(dd,'''+@Date1+''',DATEAGREED)>= 0 AND datediff(dd,DATEAGREED,'''+@Date2+''')>=0 '
set @Sql=@Sql+' group by OWNERIDNO ) a'
exec(@Sql)
end
create PROC Find(@Date1 varchar(10), @Date2 varchar(10), @PageCout int = 1,
@PageSize int = 20, @Num int Output)
AS
DECLARE @Shen int
DECLARE @Sql varchar(1000)
SELECT @Num = COUNT(*)
FROM saleinfo
WHERE datediff(dd,@Date1,DATEAGREED)>= 0 AND datediff(dd,DATEAGREED,@Date2)>= 0
......
exec(@Sql)在from (select ...) 后给表加一个别名
@PageSize int = 20)
AS
DECLARE @Shen int
DECLARE @Sql varchar(1000)
BEGIN
SELECT @Num = COUNT(*)
FROM saleinfo
WHERE datediff(dd,@Date1,DATEAGREED)>= 0 AND datediff(dd,DATEAGREED,@Date2)>= 0
print cast(@Num as varchar(10)) + 'row'
set @Shen=@Num-(@PageCout-1)*@PageSize
print cast(@Shen as varchar(10)) + 'row'set @Sql='select top '+convert(varchar(10),@PageSize)+' OWNERIDNO,icount,sfmj,sfje
from (select top '+convert(varchar(10),@Shen)+' OWNERIDNO,Count(*) icount,sum(HOUSEAREA) sfmj ,
sum(TOTAMOUNT) sfje from saleinfo where datediff(dd,'''+@Date1+''',DATEAGREED)>= 0 AND
datediff(dd,DATEAGREED,'''+@Date2+''')>=0 group by OWNERIDNO order by OWNERIDNO) tem'
exec(@Sql)
end
改成: print @sql
检查一下生成的SQL语句错在那里.