query runs fine as sql query returns empty set when run in SBO

[dov_luft3]

the following query works fine as sql query

if there is null value for @CrdSlp = '' the query returns all records

if @CrdSlp = '6' has value , the query returns recodrs where slpCode= 6

when i run this query in SBO and chose no slpCode in the query Selection Criteria the query odes not return any value

//* select * from oinv t1 */

* select * from ocrd t2 */

declare @startdate datetime , @EndDate datetime , @Sellet varchar, @ledtype varChar, @leaddate date, @kupa char , @CrdSlp varchar (4)

set @enddate =/* t1.docDate */ '[%1]' set @startdate = /* t1.docDate */ '[%0]'

set @CrdSlp = /* t2.slpCode */ '[%2]'

with tb1 as

( select t0.CardCode as CarsCode ,t0.CardName as CardName,convert(date,t0.CreateDate,103) as creDate,@EndDate as parm, datediff(day,@EndDate,t0.CreateDate)as datdif,t0.SlpCode as crdSlp, t0.U_LeadType as leadType,t0.U_Sellers as seller

from OCRD t0

where t0.CardName is Not null

and (datediff(day,t0.CreateDate,@EndDate))<=365

and t0.CreateDate<=@EndDate

and (@CrdSlp= ' ' or t0.SlpCode=@CrdSlp)

)

[Johan_H]

Hi,

When you run the query in B1, and you leave the parameter empty, it will not pass a NULL value to that parameter, but instead it will pass a zero-length string (''). Please try to adapt the last line from:

and (@CrdSlp= ' ' or t0.SlpCode=@CrdSlp)

to

and ISNULL(t0.SlpCode, '') = @CrdSlp

Regards,

Johan