Thanks Joao,

It works , but i need one more help

there is extension in that query's  where clause

 and
   (
        CASE WHEN @p_template = 1 THEN {AssertRegister}.[StatusId]=1
          WHEN @p_template = 2 THEN {AssertRegister}.[StatusId] in (2,3) and {AssertRegister}.[isActive]='true'
     END
    ) 

i tried this but it shows error near to '='.

depending on @p_template i need to check different attributes

Regards,

Nilam Patil

Hi Nilam,

You need to set a boolean condition in the AND and the CASE just gives you a value.

Try this:

 and
   {AssertRegister}.[StatusId] =
        CASE WHEN @p_template = 1 THEN 1
          WHEN @p_template = 2 THEN 2
        END
    ) 

or even simpler:

 and {AssertRegister}.[StatusId] = @p_template

Let me know how it goes.

Cheers,

João

sorry i modified my previous reply plz chk it again.

Ok, try this then:

and (
    (@p_template = 1 and {AssertRegister}.[StatusId] = 1)
    OR
    (@p_template = 2 and {AssertRegister}.[StatusId] IN (2,3) and {AssertRegister}.[isActive] = 'true')
    ) 

It works.

It gives me the proper output. You also replied very quickly to my query.

Thanks a lot.