I don't have your tables or data so I can't really tell what's going on especially since I can't gen the actual execution plan. I did do a simple test between a lookup for a CHAR(2) @Client and 'XX', which SQL Server considers to be a VARCHAR(2) and I didn't see any type of datatype-precedence gotcha there.
I guess that if we really want to find out, we're going to need some more information for the "performance problem". See the 2nd link in my signature line for how to do that.
Shifting gears a bit, I see some strange things in your query. You GROUP BY @Client, which I believe to be unnecessary because you're only looking for 1 Client to begin with. You aggregate everything and then return only the given Client using HAVING which usually happens after the GROUP BY is done. You also limit accounts in the HAVING instead of the initial critera in the WHERE clause. I don't know if that's a part of the original problem but I believe I'd change it, none the less. Like this...
SELECT ValType = 'OBL',
Year = 'CY',
TT = NULL,
Euro = SUM(trans.amount),
date_created = GETDATE(),
FROM agr.dbo.trans AS trans
JOIN ccas.gen_users_clients AS users_clients
ON trans.client = users_clients.client
WHERE users_clients.client = @client
AND trans.client = @client
AND users_clients.[User_ID] = @user_id
AND users_clients.active = 1
AND trans.account BETWEEN '21100' AND '21270'
AND trans.period BETWEEN users_clients.afs_cy_obperiod AND users_clients.afs_pto
GROUP BY trans.account, users_clients.[user_id]
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs