我的存储过程里面是这么写的
ALTER PROCEDURE [dbo].[PRO_Priorflow_get]
@strwhere varchar(500),
......
as
begin
select @total=count(1) from PRO_Priorflow where 1=1 + @strwhere
....
end 传过来的查询条件是‘and recordtime between '2012-04-17 00:00:00' and '2012-04-20 23:59:59' ’
报错了,我这没写成@begintime和@endtime这么传特定时间是因为前台可能有多个查询条件,如果写成ALTER PROCEDURE [dbo].[PRO_Priorflow_get]
@strwhere varchar(500),
@begintime varchar(500),
@endtime varchar(500),
@no int
......
as
begin
select @total=count(1) from PRO_Priorflow where 1=1 and time between @begintime and @endtime and NO=@no
....
end 时由于前台没有选择no的查询,后台查不到数据,求怎么解决的思路
ALTER PROCEDURE [dbo].[PRO_Priorflow_get]
@strwhere varchar(500),
......
as
begin
select @total=count(1) from PRO_Priorflow where 1=1 + @strwhere
....
end 传过来的查询条件是‘and recordtime between '2012-04-17 00:00:00' and '2012-04-20 23:59:59' ’
报错了,我这没写成@begintime和@endtime这么传特定时间是因为前台可能有多个查询条件,如果写成ALTER PROCEDURE [dbo].[PRO_Priorflow_get]
@strwhere varchar(500),
@begintime varchar(500),
@endtime varchar(500),
@no int
......
as
begin
select @total=count(1) from PRO_Priorflow where 1=1 and time between @begintime and @endtime and NO=@no
....
end 时由于前台没有选择no的查询,后台查不到数据,求怎么解决的思路
set @sqls='select @a=count(*) from tableName where 1=1 and time between'+@begintime+'and'+@endtime+'and no='+@no
exec sp_executesql @sqls,N'@a int output',@total output
select @total
先
@sql = '.....code......'
exec( @sql )
set @sqls='select @a=count(*) from tableName where 1=1 and time between'+@begintime+'and'+@endtime+'and no='+ltrim(@no)
exec sp_executesql @sqls,N'@a int output',@total output
select @total
的问题
应该是' and recordtime between '''+'2012-04-17 00:00:00'+'''+' and '''+'2012-04-20 23:59:59'''
这的需求有些像报表查询一样,根据条件来查询,如果没有选择哪个条件,where语句之后就不带它
很多人都根据选择的条件个数动态的去构建不同的SQL语句,前端程序会写的太复杂
每个成熟的业务系统都有一个统一的解决方案,可以用SQL探查器跟踪分析他们的查询行为看是怎么解决的