日期在存储过程中合成查询语句的问题
请问@BDate与@EDate传入后在Set语句中应该如何写才能不出错
ALTER proc Get_adjscx
(
@bt varchar(4),
@ddid varchar(30),
@zdid varchar(30),
@Bdate varchar(10),
@Edate varchar(10)
)
as
DECLARE @SqlStr varchar(200)
Set @SqlStr= 'select * from bl_idx where BDate> = '+@BDate+ ' and Bdate <= '+@EDate
select @SqlStr
if @bt <> ' '
Set @SqlStr=@SqlStr+ ' and bt=@bt '
if @ddid <> ' '
Set @SqlStr=@SqlStr+ ' and ddid=@ddid '
if @zdid <> ' '
Set @SqlStr=@SqlStr+ ' and zdid=@ddid '
exec(@SqlStr)
go
[解决办法]
ALTER proc Get_adjscx
(
@bt varchar(4),
@ddid varchar(30),
@zdid varchar(30),
@Bdate varchar(10),
@Edate varchar(10)
)
as
DECLARE @SqlStr varchar(200)
Set @SqlStr= 'select * from bl_idx where BDate> = ' ' '+@BDate+ ' ' ' and Bdate <= ' ' '+@EDate+ ' ' ' '
select @SqlStr
if @bt <> ' '
Set @SqlStr=@SqlStr+ ' and bt= '+@bt
if @ddid <> ' '
Set @SqlStr=@SqlStr+ ' and ddid= '+@ddid
if @zdid <> ' '
Set @SqlStr=@SqlStr+ ' and zdid= '+@ddid
exec(@SqlStr)
go
[解决办法]
规定:日期要用单引号括起来,当字符串中有单引号时,必须将单引号换成二个单引号.
Set @SqlStr= 'select * from bl_idx where BDate> = ' ' '+@BDate+ ' ' ' and Bdate <= ' ' '+@EDate + ' ' ' '
