I've forgotten this more than once so it's getting posted here.
To get cfqueryparam to work properly with NVARCHAR columns in SQL Server, go into the datasource advanced options section and tick the following option:
Enable High ASCII characters and Unicode for data sources configured for non-Latin characters
There's no need to try and use N'' in the SQL as it's all taken care of for you.