执行动态SQL，exec和exec sp_executesql

两者都可以执行动态sql
sp_executesql  可以在动态 sql 中使用变量，并且将变量输出
exec 则只能执行动态 sql 和 存储过程，也可以使用表变量，执行时需要加括号，唯一不足就是不可以输出变量

create table t_test(id int,name varchar(10),value int)
declare @v_count int,@n_avgnum numeric,@v_tablename nvarchar(30),@v_sql nvarchar(4000),@v_id int
set @v_id=1
set @v_tablename='T_TEST'
begin
	set @v_sql = 'select * from '+@v_tablename
	exec(@sql)
	set @v_sql='select @v_count=count(1),@n_avgnum=avg(value) from '+@v_tablename+ ' where id='+@v_id
	exec sp_executesql @v_sql,
						N'@v_count numeric output,@n_avgnum numeric output',
						@v_count output,@n_avgnum  output -- 输出参数
	select @v_count,@n_avgnum
end

另外例子：

DECLARE @V_TEST_COUNT INT,@str_sql NVARCHAR(4000)
	SET @str_sql = N'select @tmp_counts = count(1)'
             + N'  FROM sys.objects t WHERE t.type='''+N'U'+N''' AND t.name LIKE ''T_TEST' +CAST(@yearmn AS VARCHAR(30))+ N'%''';

	EXEC sp_executesql @str_sql,N'@tmp_counts int output',
                         @tmp_counts = @V_TEST_COUNT OUTPUT;      --table cnt