EXEC和sp_executesql

EXEC：T-SQL中最早提供的一种用于执行动态SQL的方法。EXEC接受一个字符串作为在圆括号中输入的参数，执行字符串中包含的批处理代码。EXEC命令的输入既支持普通字符，也支持Unicode字符。

使用游标对INFORMATION_SCHEMA.TABLES视图进行查询，以获取TSQLFundamentals2008数据库中表的名称。对于每个表，代码将构造和执行一个批处理代码，对当前表调用sp_spaceused存储过程以获取其磁盘空间使用信息：

USE TSQLFundamentals2008;

DECLARE

    @sql AS NVARCHAR(300),

    @schemaname AS sysname,

    @tablename AS sysname;

DECLARE CR CURSOR FAST_FORWARD FOR

SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE='BASE TABLE';

OPEN CR

FETCH NEXT FROM CR 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 CR INTO @schemaname,@tablename;

END

CLOSE CR

DEALLOCATE CR

 

sp_executesql: sp_executesql是继EXEC之后引入的另一种执行动态SQL的方法。该存储过程更安全和灵活，支持输入和输出番薯。与EXEC不同的是，sp_executesql只支持使用Unicode字符串作为其输入的批处理代码。

sp_executesql [ @statement = ] statement

[ 

    { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } 

     { , [ @param1 = ] 'value1' [ ,...n ] }

]

@params: 需要指定包含想要运行的批处理代码的Unicode字符串

@param1: 一个Unicode字符串，包含输入和输出参数的生命，接着为参数指定取值，各参数之间用逗号隔开

下面构造一个对Sales.Orders表进行查询的批处理代码

USE TSQLFundamentals2008;

DECLARE @sql AS NVARCHAR(100);

SET @sql=N'SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderid=@orderid;';

EXEC sp_executesql @sql, @params=N'@orderid AS INT', @orderid=10248;

 

下面的代码静查询INFORMATION_SCHEMA.TABLES视图，获取数据库中表和视图的名称的列表，在使用游标来循环遍历对象名称。每次循环中，代码会构造一个动态SQL批处理，以查询当前对象的行数，并把结果保存在一个输出参数@n中。之后将输出参数@n中的值传递到局部变量@numrows（指定为输出参数）

USE TSQLFundamentals2008;

DECLARE @Counts 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)+')';

    EXEC sp_executesql @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

转载于:https://www.cnblogs.com/fast-michael/archive/2012/07/17/2594931.html