第10章 可编程对象(4)

--10.6 动态SQL
--SQL Server提供了两种执行动态SQL的方法:使用EXEC命令和使用sp_executesql存储过程。
--当把用户的输入拼接为代码中的一部分时，要特别小心。黑客们经常会试图注入(inject)你
--不想运行的代码。要防止SQL注入，最好的方法就是避免将用户的输入拼接为代码的一部分。
--但是如果你确实需要将用户的输入拼接为代码的一部分，务必要对用户的输入进行彻底检查，
--看看有没有sql注入的企图。

--10.6.1 Exec命令
--Exec接受一个字符串作为在圆括号中输入的参数，执行字符串中包含的批处理代码。
declare @sql as nvarchar(100);
set @sql=N'Print ''This message was printed by a dynamic sql batch...'';';
exec(@sql);
go

use TSQLFundamentals2008;
declare @sql as nvarchar(300),
@schemaname as sysname,
@tablename as sysname;
declare c cursor fast_forward for
select table_schema, table_name
from information_schema.tables
where table_type = 'BASE TABLE';

open c

fetch next from c into @schemaname, @tablename
while @@FETCH_STATUS=0
begin
set @sql = N'exec sp_spaceused N''' 
	+ QUOTENAME(@schemaname) + N'.'
	+ QUOTENAME(@tablename) + N''';';

exec(@sql);

fetch next from c into @schemaname, @tablename;
end

close c;

deallocate c;
go;

--10.6.2 sp_executesql 存储过程
--与exec命令不同的是，sp_executesql存储过程只支持使用unicode字符串作为其输入的批处理代码。
--正因为动态sql代码中可以使用输入和输出参数，这样就有助于写出更安全和更有效的代码。从安全性的角度
--来说，在代码中出现的参数并不是代码的一部分，而只是表达式中的运算对象/所以通过使用参数，就可以不
--必受sql注入的困扰了。
--sp_executesql存储过程有两个输入参数和一个参数赋值部分。在第一个参数@stmt中，需要指定包含想要运行
--的批处理代码的Unicode字符串。第二个参数@params是一个unicode字符串，包含@stmt中所有输入和输出参数
--的声明。接着为输入和输出参数指定取值，各参数之间用逗号分隔。
declare @sql as nvarchar(300);

set @sql = N'select orderid, custid, empid, orderdate 
from sales.orders 
where orderid = @orderid';

exec sp_executesql @stmt=@sql, @params=N'@orderid as int',
@orderid = 10248
go

--为了使用输出参数,只须要简单地在参数声明部分和参数赋值部分同时指定output关键字.
declare @counts as table
(schemaname sysname not null,
tablename sysname not null,
numrows int not null,
primary key(schemaname, tablename));

declare 
@sql as nvarchar(350),
@schemaname as sysname,
@tablename as sysname,
@numrows as int;

declare c cursor fast_forward for
select table_schema, table_name
from information_schema.tables

open c

fetch next from c into @schemaname, @tablename;
while @@FETCH_STATUS = 0
begin 
	set @sql = N'set @n=(select count(*) from '
	+ QUOTENAME(@schemaname) + N'.'
	+ QUOTENAME(@tablename) + N');';

	exec sp_executesql @stmt = @sql, @params = N'@n as int output', @n = @numrows output;

	insert into @counts(schemaname, tablename, numrows)
	values(@schemaname, @tablename, @numrows);
	fetch next from c into @schemaname, @tablename
end

close c;

deallocate c;
select * from @counts;