Create proc [dbo].[Report_PS_WeekSale]
@CompanyName varchar(20),
@ProjectName varchar(20),
@PhaseName varchar(20),
@BeginDate DateTime,
@EndDate DateTime
As
begin
declare @sql varchar(2000)
declare @BookArea float(20)
declare @BookSum int
declare @ContractArea float(20)
declare @ContractSum int
Declare @ContractPrice money
declare @SaleArea float(20)
declare @SaleSum int
declare @SalePrice money
declare @tempBookNum int
declare @tempsalenum int
declare @tempsaleamt money
declare @tempContractNum int
declare @tempBuildUpArea float set @sql ='select a.BookNum,a.SaleNum,a.SaleAmt,a.ContractNum,a.ContractAmt,b.BuildUpArea from View_SaleStatistic a join View_ps_unit b on a.UnitId= b.UnitId where 1=1'
if(@CompanyName is not null)
set @sql = @sql +' and b.CmpanyName like ''%'+@CompanyName+'%'''
if(@ProjectName is not null)
set @sql = @sql +' and b.ProjectName like ''%'+@ProjectName+'%'''
if(@PhaseName is not null)
set @sql = @sql +' and b.PhaseDesc like ''%'+@PhaseName+'%'''
if(@BeginDate is not null)
set @sql = @sql +' and a.SaleDate >='''+cast(@BeginDate as varchar(10))+''''
if(@EndDate is not null)
set @sql = @sql +' and a.SaleDate <='''+cast(@EndDate as varchar(10))+'''' -- print @Sql; declare mycursor cursor for @sql
open mycursor
FETCH NEXT FROM mycursor INTO @tempBookNum,@tempsalenum,@tempsaleamt,@tempContractNum,@tempBuildUpArea
while (@@fetch_status=0)
begin
print '1'
FETCH NEXT FROM mycursor INTO @tempBookNum,@tempsalenum,@tempsaleamt,@tempContractNum,@tempBuildUpArea
end
close mycursor
deallocate mycursor
end
GO
@CompanyName varchar(20),
@ProjectName varchar(20),
@PhaseName varchar(20),
@BeginDate DateTime,
@EndDate DateTime
As
begin
declare @sql varchar(2000)
declare @BookArea float(20)
declare @BookSum int
declare @ContractArea float(20)
declare @ContractSum int
Declare @ContractPrice money
declare @SaleArea float(20)
declare @SaleSum int
declare @SalePrice money
declare @tempBookNum int
declare @tempsalenum int
declare @tempsaleamt money
declare @tempContractNum int
declare @tempBuildUpArea float set @sql ='select a.BookNum,a.SaleNum,a.SaleAmt,a.ContractNum,a.ContractAmt,b.BuildUpArea from View_SaleStatistic a join View_ps_unit b on a.UnitId= b.UnitId where 1=1'
if(@CompanyName is not null)
set @sql = @sql +' and b.CmpanyName like ''%'+@CompanyName+'%'''
if(@ProjectName is not null)
set @sql = @sql +' and b.ProjectName like ''%'+@ProjectName+'%'''
if(@PhaseName is not null)
set @sql = @sql +' and b.PhaseDesc like ''%'+@PhaseName+'%'''
if(@BeginDate is not null)
set @sql = @sql +' and a.SaleDate >='''+cast(@BeginDate as varchar(10))+''''
if(@EndDate is not null)
set @sql = @sql +' and a.SaleDate <='''+cast(@EndDate as varchar(10))+'''' -- print @Sql; declare mycursor cursor for @sql
open mycursor
FETCH NEXT FROM mycursor INTO @tempBookNum,@tempsalenum,@tempsaleamt,@tempContractNum,@tempBuildUpArea
while (@@fetch_status=0)
begin
print '1'
FETCH NEXT FROM mycursor INTO @tempBookNum,@tempsalenum,@tempsaleamt,@tempContractNum,@tempBuildUpArea
end
close mycursor
deallocate mycursor
end
GO
set @sql ='insert into #temp(...) select a.BookNum,a.SaleNum,a.SaleAmt,a.ContractNum,a.ContractAmt,b.BuildUpArea from View_SaleStatistic a join View_ps_unit b on a.UnitId= b.UnitId where 1=1'
if(@CompanyName is not null)
set @sql = @sql +' and b.CmpanyName like ''%'+@CompanyName+'%'''
if(@ProjectName is not null)
set @sql = @sql +' and b.ProjectName like ''%'+@ProjectName+'%'''
if(@PhaseName is not null)
set @sql = @sql +' and b.PhaseDesc like ''%'+@PhaseName+'%'''
if(@BeginDate is not null)
set @sql = @sql +' and a.SaleDate >='''+cast(@BeginDate as varchar(10))+''''
if(@EndDate is not null)
set @sql = @sql +' and a.SaleDate <='''+cast(@EndDate as varchar(10))+'''' exec( @Sql); declare mycursor cursor for #temp
open mycursor
消息 102,级别 15,状态 1,过程 Report_PS_WeekSale,第 41 行
'@sql' 附近有语法错误。
--------------
這樣寫有問題:)
for後面要一個Select語句
不曉得這樣可以不
exec ('declare mycursor cursor for'+@sql)
--->
exec('declare mycursor cursor for ' + @sql)