If my stored procedure contains the statement below. It works.
select top 10 ITMREF_0 as ITMREF,ITMDES1_0 as ITMDES from ...ITMMASTER
If I change to dynamic sql below,
declare @sqlTextAll nvarchar(max)
set @sqlTextAll='select top 10 ITMREF_0 as ITMREF,ITMDES1_0 as ITMDES from ...ITMMASTER'
exec @sqlTextAll
it say "
[Microsoft][SQL Server Native Client 11.0][SQL Server]EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time. : SQL Server Error
"
This is a sample to explain my problem. My case has to use dynamics table and field names. That is why dynamics sql is involved.