I hear ya about unicode and can contribute this:
To execute dynamic SQL (I have pretty extensive experience with the stuff), you have to store the command you're about to execute in Unicode. Using a standard varchar won't work, so you have to use an nvarchar. SP_EXECUTESQL just won't work unless the supplied final string is unicode.
So, it just makes sense that if SQL Server is going to panic and run to dynamic SQL for a stored proc, that it would declare a unicode variable and then store it as such.
I'm working around this for two reasons:
1. The execution cache issue;
2. nvarchar is capped at 4000 characters in SQL 2000. SQL 2005 has a new nvarchar(max) variable that is supposedly good for 2GB-length text strings on 64 bit installations.
I've run into the 4000 character cap in the past, but am unable to depricate the nvarchar(4000) variable in favor of nvarchar(max) and mandate SQL 2005 due to customer support and political reasons. Have to maintain SQL 2000 compatibility for at least the next couple years.
That issue is for an unrelated product to this issue though, and is the deliberate reason why I've avoided dynamic SQL and instead used variable driven logic branching and completely separate queries in the procs based on those variable options.
My txt files that store the CREATE PROCEDURE arguments for my VB front end don't have an ounce of unicode in them. I'm feeding them into SQL Server via this method, more or less:
(VB Code)
Try
fsSQLFileReader = My.Computer.FileSystem.OpenTextFileReader(strScriptDirectory & "my_procedure.sql")
strSQLString = fsSQLFileReader.ReadToEnd
ProgressBar1.Increment(1)
srvSQL.ConnectionContext.ExecuteNonQuery(strSQLString, ExecutionTypes.Default)
Catch ex As Exception
Me.TextBox1.AppendText("ERROR: File not found or invalid syntax in file!" & vbCrLf)
Exit Sub
End Try
Never raises an exception during execution when it installs the proc... Just installs nice and clean.