Dynamic SQL and escape characters

We have many dynamic sql stored procedures in our system

I am finding that on "search style" stored procedures I am running into issues where the DATA has a single quote in it
The parameter has an apostrophe has a single quote in it
But the dynamic SQL does an escape on the EXEC (@SQL)
Is there a "good way" to handle this?


Example...
Here is some code

DECLARE @SQL VARCHAR(MAX) = '';
DECLARE @param VARCHAR(10) = 'a''.com';
--SELECT @param;

SET @SQL = @SQL + 'select * from users where Username = ''' + @param + ''' order by username ';
PRINT @SQL;
EXEC ( @SQL );

Select allOpen in new window

And here is the message on execute