动态sql详谈动态指定表名 列名(exec sql_executesql)

--在动态sql中，无论exec还是exec sp_executesql,都不允许使用参数形式的表名和列名,
--你可以使用变量或者在存储过程中使用存储过程参数声明部分的参数，而不是直接使用
--exec sp_executesql参数声明部分的参数

--典型的错误代码(there is no wrong with grammer,but not any row is returned)
declare @outPutValue as int,
@schemaname as nvarchar(50),
@tablename as nvarchar(50),
@filtercolumn as nvarchar(50),
@columnvalue as nvarchar(50),
@sql nvarchar(max);
set @schemaname='dbo';
set @tablename='orders';
set @filtercolumn='employeeid';
set @columnvalue='1';
set @sql='select count(*) from '+quotename(@schemaname)+'.'+quotename(@tablename)
+' where @fc=@cv ';--使用了参数形式的列名 the error occur
exec sp_executesql
@sql,
N'@fc as nvarchar(50),@cv as nvarchar(50),@outValue as int output',
@fc=@filtercolumn,
@cv=@columnvalue,
@outValue=@outPutValue output;
print @outPutValue;
print @sql;

--solution 1-----使用变量的方式为动态sql静态提供选择列，表名，过滤列
--此解决方案并展示了如何使用exec sp_executesql的输出参数
use northwind;
go
declare @outPutValue as int,
@schemaname as nvarchar(50),
@tablename as nvarchar(50),
@filtercolumn as nvarchar(50),
@columnvalue as nvarchar(50),
@countname as nvarchar(50),
@sql nvarchar(max);
set @schemaname='dbo';
set @tablename='orders';
set @filtercolumn='employeeid';
set @columnvalue='1';
set @countname='orderid';
set @sql='select @outValue=count('+@countname --使用了输出参数
+') from '
+quotename(@schemaname)+'.'+quotename(@tablename)
+' where '+@filtercolumn+'=@cv ';
exec sp_executesql
@sql,
N'@fc as nvarchar(50),@cv as nvarchar(50),@outValue as int output',
@fc=@filtercolumn,
@cv=@columnvalue,
@outValue=@outPutValue output;--使用输出参数为变量@outPutValue赋值，变量后加关键字output
print @outPutValue;
print @sql;
go

--solution 2-使用存储过程的参数为动态sql动态提供选择列，表名，过滤列
use NorthWind;
go
alter PROCEDURE GetData
@tbName nvarchar(50),
@colName nvarchar(50),
@Name nvarchar(50),
@filtername nvarchar(50)
AS
BEGIN
declare @sql nvarchar(max);
set @sql='select '+ @colName+' from ' +@tbName+ ' where '+@filtername+'=@whereName';
--注意此句不可以写成如下:
-- set @sql='select @colName from @tbName where employeeid=@whereName';
exec sp_executesql
@sql,
N'@whereName nvarchar(20)',
@Name
print @sql;
END

exec GetData
@name=N'1',
@tbName=N'dbo.Orders',
@colName=N'employeeid',
@filtername=N'employeeid';

 

conclusion:solution1 和solution 2 都成功避开了，不能直接使用参数形式的列名和表名的限制，
solution1使用的变量的方式是静态的，
solution2使用的是存储过程的参数的方式是动态的，这种方式可用性更强。