Q: Parameterized EXECUTE( @lSqlStr )?

All,


I execute dynamic SQL with a return parameter like this:


SET @lSqlStr = ' SELECT @oRsltCount = COUNT(*) FROM foo';

EXECUTE sp_executesql @lSqlStr, N'@oRsltCount int out' @oRsltCount out;

The sp_executesql @stmt parameter is type nVarChar. Occationally, the size of @lSqlStr may be greater than 4000 bytes, which exceed the maximum allowable size of type nVarchar.

So, I may execute dynamic SQL like this:

EXECUTE ( @lSqlStr );

where the type of the argument is VarChar(MAX), and size of @lSqlStr may be up to 2^31 -- but cannot parameterize (or bind, if you will) the query result.

How can I execute dynamic query with string size greater than 4k bytes AND bind the query result?

-Kevin