The Hint FORCESEEK, seems to do the trick.

Analysing the "bad" execution plan, I notice that the searches where done with index scan instead of index seek, incresing drastically the reads...

Finally I got the good and bad plan.

Bad Plan

Bad Plan

Good Plan

Good Plan

This is the QUERY constructed.

declare @Destinations table (Destination nvarchar(50))

insert into @Destinations (Destination)

Select Distinct 'K2SQL:' + CODE collate Latin1_General_CI_AS as destination from Claims.dbo.t_usersEntityRelation A

inner join (

...

It's getting complicated to get the bad execution plans. I'm using profiler -> performance events -> show plan all and show plan XML

But after some minutes I get " There...

Hi thank you for the replies.

What I meant with dinamic query is that part of the SQL code is passed to the stored procedure as a parameters. EXample:

exec SP_SELECT_ROWS_K2_CLAIMS_WF @SectionId=13,',@FirstRow=1,@RowCount=1500,...