存储过程使用动态条件时,出现从" 数据类型 varchar 转换为 float 时出错"的问题

[ahking]

存储过程如下:
ALTER PROCEDURE [dbo].[pp]
(
@Scope float =0.2,
@FilterCon int=0
)
AS
BEGIN
declare @strWhere varchar(500)
declare @strMain varchar(500)
if @FilterCon = 0
begin
set @strWhere = '....'
end
if @FilterCon = 1
begin
set @strWhere = ' ....'
end

set @strMAIN ='SELECT * FROM
(
SELECT
Code,
avgPrice = AVG(p1),
minPrice = MIN(p2),
LastDate = MAX(Date)
FROM Datab
GROUP BY Code
) A
INNER JOIN Datab B
ON A.Code = B.Code AND A.LastDate = B.Date
WHERE (A.avgPrice-B.p1) >= A.avgPrice*'+@Scope + @strWhere

exec(@strMAIN)

END

[-晴天]

WHERE (A.avgPrice-B.p1) >= A.avgPrice*'+ltrim(@Scope) + @strWhere  

[水族杰纶]

+@Scope 
--》
+ltrim(@Scope) 

[中国风]

ALTER PROCEDURE [dbo].[pp]
(
@Scope float =0.2,
  @FilterCon int=0
)
AS
BEGIN
declare @strWhere varchar(500)
declare @strMain varchar(500)
if @FilterCon = 0
begin 
set @strWhere = '....'
end
if @FilterCon = 1 
begin 
set @strWhere = ' ....'
end

set @strMAIN ='SELECT * FROM
(
  SELECT
  Code,
  avgPrice = AVG(p1),
  minPrice = MIN(p2),
  LastDate = MAX(Date)  
  FROM Datab
  GROUP BY Code
) A
INNER JOIN Datab B
ON A.Code = B.Code AND A.LastDate = B.Date 
WHERE (A.avgPrice-B.p1) >= A.avgPrice*'+CAST(@Scope AS VARCHAR(100)) + @strWhere 

exec(@strMAIN)  

END

CAST(@Scope AS VARCHAR(100))--改改

[hao1hao2hao3]

WHERE (A.avgPrice-B.p1) >= CAST ( A.avgPrice * '+ @Scope + ' AS VARCHAR(20)) '  + @strWhere

[ahking]

这样感觉速度很慢,有没有替代的方法?

[--小F--]

set @strMAIN ='SELECT * FROM
(
  SELECT
  Code,
  avgPrice = AVG(p1),
  minPrice = MIN(p2),
  LastDate = MAX(Date)   
  FROM Datab
  GROUP BY Code
) A
INNER JOIN Datab B
ON A.Code = B.Code AND A.LastDate = B.Date  
WHERE (A.avgPrice-B.p1) >= A.avgPrice*'+ltrim(@Scope) + @strWhere  

[-晴天]

[Quote=引用 5 楼 ahking 的回复:]
这样感觉速度很慢,有没有替代的方法?
[/Quote]

最好不要用动态的,可以可以考虑不用 @where ,而是在不同 @FilterCon 时,执行三条不同的语句,这样,虽然写的内容长一些,但执行速度肯定要快.

[勿勿]

WHERE (A.avgPrice-B.p1) >= A.avgPrice*'+@Scope + @strWhere

=====》
ltrim(@Scope)

or

cast（@Scope as varchar（100））