我的sql存储过程要实现查某个范围的价格的商品，
select * from sale where 1=1 and price between price1 and price2 and .....
如果传来的price1和price2都是0，则忽略价格限制
我想开始就判断price1和price2是否都为0，[email protected]，用于替换查询条件and price between price1 and price2，但不知道如何写，请指教！
------解决方案--------------------
- SQL code
select * from sale where 1=1 and price between (case when price1=0 then price else price1 end) and (case when price2=0 then price else price2 end) and ...
------解决方案--------------------
- SQL code
DECLARE @price1 DECIMAL(12,5),@price2 DECIMAL(12,5)DECLARE @temp VARCHAR(500)DECLARE @sql VARCHAR(1000)SET @temp = ''set @sql = 'select * from sale where 1=1 'IF Not (@price1 = 0 AND @price2 = 0)BEGIN SET @sql = @sql + ' where price between [email protected]+' and [email protected]END EXEC(@sql)GO
------解决方案--------------------
拼接动态sql