(@dateks varchar(20) , @datejs varchar(20) , @datelx varchar(20))
AS
declare @Sql varchar(1000)
set @Sql=' 1=1 'if(@datelx='''1''')
set @Sql=@Sql+'and (CONVERT(varchar(100), qdrq, 23) between '+@dateks+' and '+@dateks
if(@datelx='''2''')
set @Sql=@Sql+' and (CONVERT(varchar(100), fhrq, 23) between '+@dateks+' and '+@dateks
if(@datelx='''3''')
set @Sql=@Sql+' and (CONVERT(varchar(100), sjshrq, 23) between '+@dateks+'and '+@dateks
set @Sql='select * from htxx where'+@Sql
exec(@Sql)其中,@datelx 是用来判断日期类型的,有三种类型,分别是1、2、3 该语句在运行的时候老是会提示'2012-01-01' 附近有语法错误。2012-01-01 是我的开始日期 不知道怎么办了~~望高手能够解答一下!
AS
declare @Sql varchar(1000)
set @Sql=' 1=1 'if(@datelx='''1''')
set @Sql=@Sql+'and (CONVERT(varchar(100), qdrq, 23) between '+@dateks+' and '+@dateks
if(@datelx='''2''')
set @Sql=@Sql+' and (CONVERT(varchar(100), fhrq, 23) between '+@dateks+' and '+@dateks
if(@datelx='''3''')
set @Sql=@Sql+' and (CONVERT(varchar(100), sjshrq, 23) between '+@dateks+'and '+@dateks
set @Sql='select * from htxx where'+@Sql
exec(@Sql)其中,@datelx 是用来判断日期类型的,有三种类型,分别是1、2、3 该语句在运行的时候老是会提示'2012-01-01' 附近有语法错误。2012-01-01 是我的开始日期 不知道怎么办了~~望高手能够解答一下!
(@dateks varchar(20) , @datejs varchar(20) , @datelx varchar(20))
AS
declare @Sql varchar(1000)
set @Sql=' 1=1 'if(@datelx='''1''')
set @Sql=@Sql+' and (CONVERT(varchar(100), qdrq, 23) between '''+@dateks+''' and '''+@dateks+''''
if(@datelx='''2''')
set @Sql=@Sql+' and (CONVERT(varchar(100), fhrq, 23) between '''+@dateks+''' and '''+@dateks+''''
if(@datelx='''3''')
set @Sql=@Sql+' and (CONVERT(varchar(100), sjshrq, 23) between '''+@dateks+''' and '''+@dateks+''''
set @Sql='select * from htxx where'+@Sql
exec(@Sql)
此查询中,还包含有其它条件的查询,我把语句精简了一下~~其它地方都没有问题了,就是在日期这块有问题。我把完整的语句贴上去,树哥给看一下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[search]
(@htbhcx varchar(20),@ywycx varchar(20),@ywbmcx varchar(20), @kpqkcx varchar(20) ,@fkqkcx varchar(20) ,@cgshztcx varchar(20),@shztcx varchar(20), @shqkcx varchar(20),@skztcx varchar(20),@dateks varchar(20) , @datejs varchar(20) , @datelx varchar(20))
AS
declare @Sql varchar(1000)
set @Sql=' 1=1 '
if(@htbhcx<>'')
set @Sql=@Sql+' and htbh='+@htbhcx
if(@ywycx<>'')
set @Sql=@Sql+' and ywy='+@ywycx
if(@ywbmcx<>'')
set @Sql=@Sql+' and ywbm='+@ywbmcx
if(@kpqkcx='''1''')
set @Sql=@Sql+' and ykpje=0'
if(@kpqkcx='''2''')
set @Sql=@Sql+' and ykpje<htje'
if(@kpqkcx='''3''')
set @Sql=@Sql+' and ykpje<=htje'
if(@kpqkcx='''4''')
set @Sql=@Sql+' and ykpje>=htje'
if(@fkqkcx='''1''')
set @Sql=@Sql+' and yfkje=0'
if(@fkqkcx='''2''')
set @Sql=@Sql+' and yfkje>0 and yfkje<(yskje-hjjxjg)'
if(@fkqkcx='''3''')
set @Sql=@Sql+' and yfkje>=(yskje-hjjxjg)'
if(@cgshztcx<>'')
set @Sql=@Sql+' and cgshzt='+@cgshztcx
if(@shztcx<>'')
set @Sql=@Sql+' and shzt='+@shztcx
if(@shqkcx<>'')
set @Sql=@Sql+' and shqk='+@shqkcx
if(@skztcx='''1''')
set @Sql=@Sql+' and yskje=0'
if(@skztcx='''2''')
set @Sql=@Sql+' and yskje>0 and yskje<htje'
if(@skztcx='''3''')
set @Sql=@Sql+' and yskje>=0 and yskje<htje'
if(@skztcx='''4''')
set @Sql=@Sql+' and yskje>=htje'
if(@datelx='''1''')
set @Sql=@Sql+' and (CONVERT(varchar(100), qdrq, 23) between '''+@dateks+''' and '''+@dateks+''''
if(@datelx='''2''')
set @Sql=@Sql+' and (CONVERT(varchar(100), fhrq, 23) between '''+@dateks+''' and '''+@dateks+''''
if(@datelx='''3''')
set @Sql=@Sql+' and (CONVERT(varchar(100), sjshrq, 23) between '''+@dateks+''' and '''+@dateks+''''
set @Sql='select * from htxx where'+@Sql
exec(@Sql)这个是我的执行语句
USE [SalesSystem]
GODECLARE @return_value intEXEC @return_value = [dbo].[search]
@htbhcx = NULL,
@ywycx = NULL,
@ywbmcx = NULL,
@kpqkcx = NULL,
@fkqkcx = NULL,
@cgshztcx = NULL,
@shztcx = NULL,
@shqkcx = NULL,
@skztcx = NULL,
@dateks = N'''2012-01-01''',
@datejs = N'''2012-01-31''',
@datelx = N'''1'''SELECT 'Return Value' = @return_valueGO