Reporting services，输入参数组合sql的问题

[yuemb369]

其中CustomerNo是要在报表中显示的字段，@StartDate是报表参数，我的基本想法是：如果@StartDate为空,就查全部,如果不为空,就加入查询条件. 运行报表有问题,但是我在设计报表时手动测试查询（就是点那个!号），输入参数值，是可以运行的。

SQL:

declare @sql nvarchar(4000)
declare @sqlWhere nvarchar(4000)

set @sql='select CustomerNo from table1 '
set @sqlWhere=''


if @StartDate is not null and @StartDate<>''
begin
set @sqlWhere =@sqlWhere + ' StartDate =''' +@StartDate + ''''
end

if ltrim(rtrim(@sqlWhere))<>''
begin
set @sqlWhere =' where ' + @sqlWhere
end
set @sql=@sql+@sqlWhere

exec sp_executeSQL @sql


------------------------
Error message:
[rsFieldReference] The Value expression for the textbox ‘CustomerNo’ refers to the field ‘CustomerNo’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

如果我不加入这个条件，只是
set @sql='select CustomerNo from table1 '
也不会出问题

[yuemb369]

没有分了，我只有十分

[yuemb369]

up

[kid_1982]

set @sqlWhere =@sqlWhere + ' StartDate =''' +@StartDate + ''''
改为
set @sqlWhere =@sqlWhere + ' StartDate =' + ''' +@StartDate + '''
呢?

[yuemb369]

kid_1982() ( ) 信誉：100 2007-8-2 9:56:42 得分: 0



set @sqlWhere =@sqlWhere + ' StartDate =''' +@StartDate + ''''
改为
set @sqlWhere =@sqlWhere + ' StartDate =' + ''' +@StartDate + '''
呢?



不是这个原因，是因为我没有在Parameters里指定StartDate等的对应值。谢谢你的回复

[yaoleshi]

我还可以接几分