The only difference is that instead of EXEC (@cmd) you would use EXEC sp_executesql @cmd. Technically speaking, sp_executesql takes NVARCHAR arguments. In order to create an NVARCHAR string you should prefix the string with N like so:
DECLARE @cmd NVARCHAR(1000);
SET @cmd = N'My string'
This denotes that the string is a Unicode string which may contain national character sets. Of course you can omit the N but if you have anything other than ASCII characters in it then it won't work properly and you'll end up with strings that contain a lot of question marks instead of your special characters (CJK - Chinese, Japanese, Korean - character sets for instance) so it is good practice to always prefix NVARCHAR strings with the N.
Some of your confusion arises because you are replacing the string concatenation operator, +, with the national character set identifier, N. You will need both. It is always worth including proper spacing in your code, especially in dynamic SQL because it's easier to read. My preference is to add the spaces at the end of lines where possible, but that's just my personal preference. Also, drop the CHAR(13) stuff unless you really want to print it out in a certain way. Your example would become:
DECLARE @cmd NVARCHAR(1000) --Note this is NVARCHAR, not VARCHAR.
SET @cmd = N'WITH vdate AS '
+ N'(SELECT valueID, '
+ N'username) '
+ N'FROM dbo.table1 '
+ N'WHERE username = ''' + convert(NVARCHAR(11), @username, 106) + ''' AND ' --Note that you are missing a chunk here - AND what?
EXEC sp_executesql @cmd
Note that your CONVERT is incorrect: Firstly you should convert to NVARCHAR (if @username isn't already), and secondly the 106 part does nothing - that's for date formatting. Also you are missing the end of the statement as there is an AND and then nothing. It's always good to use PRINT @cmd to see if the SQL you've generated is valid (it will output the contents of @cmd and you can then copy that and paste it into a workshet in SSMS).
Now, you might want to have output parameters in your dynamic SQL as well which is no problem.
DECLARE @ID INT;
DECLARE @outputValue NVARCHAR(50);
DECLARE @cmd NVARCHAR(1000);
SET @cmd = N'SELECT @value = Value FROM MyTable WHERE ID = ' +
CAST(@ID AS NVARCHAR(5));
EXEC sp_executesql @cmd, '@value NVARCHAR(50) OUT', @outputValue OUT;
SELECT @outputValue;
Traditionally, the main reason for wanting to use sp_executesql as opposed to just EXEC is that you have a better chance of the execution plan being cached and therefore re-used later. I confess I'm not sure if that's still true or not, but sp_executesql is generally the preferred method of executing dynamic SQL. A lot of poeple have an instinctive hatred of dynamic SQL but frankly, like all things, it has it's uses so long as it isn't abused and used where it just isn't required.
