CREATE PROCEDURE pro_temp
@strdtbgn datetime,@strdtend datetime,@Custom varchar(20)='',@QuotationNo varchar(20)='',@ProductNo varchar(20)='',@Audit int=-1 AS
declare @tmpdt datetime,@TmpAudit varchar(20),@TmpSend varchar(20),@strsql varchar(8000),@whereSql varchar(4000)
if @strdtbgn>@strdtend
begin
set @tmpdt = @strdtbgn
set @strdtbgn = @strdtend
set @strdtend = @tmpdt
end
if not (@custom='全部' or @custom='' or @custom='')
begin
set @whereSql =@whereSql + 'and (t.客户 like '+@custom+')'
end if not ( @QuotationNo =''or @QuotationNo='%')
begin
set @whereSql =@whereSql + 'and (t.报价单号 like '+@QuotationNo+')'
end if not (@ProductNo='' or @ProductNo='%')
begin
set @whereSql =@whereSql + 'and (t. 模糊查 like '+@ProductNo+')'
endif @Audit=-1
begin
SET @strsql='select * from qry_QuotationVerify t where
t.报价日期 between @strdtbgn and @strdtend and
ID=(
SELECT MIN(ID)
FROM qry_QuotationVerify
WHERE CHECKSUM(t.报价单号,t.产品编号,t.设计编号,t.名称)=CHECKSUM(报价单号,产品编号,设计编号,名称)
)'EXEC(@strsql)
end if @Audit=0
begin
set @tmpaudit=''
set @tmpsend=''
end
if @Audit=1
begin
set @tmpaudit=''
set @tmpsend='+'
end
if @Audit=2
begin
set @tmpaudit='√'
set @tmpsend='+'
endbegin
Set @strsql='select * from qry_QuotationVerify t where
t.报价日期 betwee @strdtbgn and @strdtend
and '+@whereSql+' and (t. 报 = '+@tmpsend+') and (t. 审= '+@tmpaudit+') and ID=(
SELECT MIN(ID)
FROM qry_QuotationVerify
WHERE CHECKSUM(t.报价单号,t.产品编号,t.设计编号,t.名称)=CHECKSUM(报价单号,产品编号,设计编号,名称)
)'
PRINT @strsql
--EXEC(@strsql)
end
GO
主要是 Set @strsql那句的 t.报价日期 betwee @strdtbgn and @strdtend
and '+@whereSql+' 不知道怎么写
@strdtbgn datetime,@strdtend datetime,@Custom varchar(20)='',@QuotationNo varchar(20)='',@ProductNo varchar(20)='',@Audit int=-1 AS
declare @tmpdt datetime,@TmpAudit varchar(20),@TmpSend varchar(20),@strsql varchar(8000),@whereSql varchar(4000)
if @strdtbgn>@strdtend
begin
set @tmpdt = @strdtbgn
set @strdtbgn = @strdtend
set @strdtend = @tmpdt
end
if not (@custom='全部' or @custom='' or @custom='')
begin
set @whereSql =@whereSql + 'and (t.客户 like '''+@custom+''')'
end if not ( @QuotationNo =''or @QuotationNo='%')
begin
set @whereSql =@whereSql + 'and (t.报价单号 like '''+@QuotationNo+''')'
end if not (@ProductNo='' or @ProductNo='%')
begin
set @whereSql =@whereSql + 'and (t.模糊查 like '''+@ProductNo+''')'
endif @Audit=-1
begin
SET @strsql='select * from qry_QuotationVerify t where
t.报价日期 between '''+@strdtbgn+''' and '''+@strdtend+''' and
ID=(
SELECT MIN(ID)
FROM qry_QuotationVerify
WHERE CHECKSUM(t.报价单号,t.产品编号,t.设计编号,t.名称)=CHECKSUM(报价单号,产品编号,设计编号,名称)
)'EXEC(@strsql)
end if @Audit=0
begin
set @tmpaudit=''
set @tmpsend=''
end
if @Audit=1
begin
set @tmpaudit=''
set @tmpsend='+'
end
if @Audit=2
begin
set @tmpaudit='√'
set @tmpsend='+'
endbegin
Set @strsql='select * from qry_QuotationVerify t where
t.报价日期 betwee '''+@strdtbgn+''' and '''+@strdtend +'''
and '+@whereSql+' and (t.报 = '''+@tmpsend+''') and (t.审= '''+@tmpaudit+''') and ID=(
SELECT MIN(ID)
FROM qry_QuotationVerify
WHERE CHECKSUM(t.报价单号,t.产品编号,t.设计编号,t.名称)=CHECKSUM(报价单号,产品编号,设计编号,名称)
)'
PRINT @strsql
--EXEC(@strsql)
end
从字符串转换为 datetime 时发生语法错误。
时间那个怎么转换
t.报价日期 betwee ''' + ltrim(@strdtbgn) + ''' and ''' + ltrim(@strdtbgn) + ''''
ltrimcast(varchar(10),@strdtbgn,126)-----------convert(varchar,@strdtbgn,126)其它datetime的变量照搬