（4.58）sp_prepare，sp_execute，sp_unprepare

【0】概念

官网参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-prepare-transact-sql?view=sql-server-ver15

sp_prepare用于参数化一个特定模式的sql语句，并返回句柄(handle)，之后的sql语句就可以使用这个句柄来传递不同的参数。

使用sp_prepare，可是让不同的参数的语句共用一个查询计划，避免声称不同的计划，从而节省编译时间。

【1】sp_prepare 与 sp_execute 以及 sp_unprepare

【1.1】sp_prepare （生成文件句柄）

sp_prepare handle OUTPUT, params, stmt, options

• handle
  是SQL Server生成的准备好的句柄标识符。handle是具有int返回值的必需参数。

• params
  标识参数化的语句。变量的params定义代替了语句中的参数标记。params是必需的参数，它要求输入ntext，nchar或nvarchar输入值。如果该语句未参数化，请输入NULL值。

• stmt
  定义游标结果集。stmt参数是必需的，它需要一个ntext，nchar或nvarchar输入值。

• options
  一个可选参数，用于返回游标结果集列的描述。options需要以下int输入值。

【1.2】sp_execute 基本语法（执行文件句柄）

sp_execute handle OUTPUT [,bound_param ] [,...n ] ]

句柄

　　Sp_prepare 返回的 句柄 值。 句柄 是一个参数，它需要调用 int 输入值。

bound_param
　　指示使用其他参数。 bound_param 是必需的参数，该参数调用任意数据类型的输入值来表示过程的附加参数。

 备注

　　bound_param 必须与 sp_prepare 参数 值所进行的声明相匹配，并且可以采用 @name = 值 或 值 形式。

【1.3】sp_unprepare 基本语法（销毁/释放文件句柄）

sp_unprepare handle   

【2】案例

1）数据准备：

create table testtable(id int ,c1 int)

go

insert testtable values(1,2)
insert testtable values(1,3)
insert testtable values(2,1)

go

create index index1 on testtable(id)

2）使用 sp_prepare的例子：

declare @N int

exec sp_prepare @n output,N'@p1 int',N'select *From testtable where id=@p1'    --这里的 N'@p1 int' 就是定义参数，这个参数应用在 第3个参数SQL语句中
exec sp_execute @n,1 ---@n就是sp_prepare返回的句柄，使用sp_execute来通过这个句柄来传递参数，这个 1就是 给@p1传参数
exec sp_execute @n,2

这两个语句的执行效果相当于：

select *From testtable where id=1
select *From testtable where id=2

但是会使用同样的查询计划，且只编译了一次。

3）官网例子：

DECLARE @P1 INT;  
EXEC sp_prepare @P1 OUTPUT,   
    N'@P1 NVARCHAR(128), @P2 NVARCHAR(100)',  
    N'SELECT database_id, name FROM sys.databases WHERE name=@P1 AND state_desc = @P2';  
EXEC sp_execute @P1, N'tempdb', N'ONLINE';  
EXEC sp_unprepare @P1;  

4）使用句柄执行语句

-- Prepare query
DECLARE @P1 INT;  
EXEC sp_prepare @P1 OUTPUT,   
    N'@Param INT',  
    N'SELECT *
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID
WHERE SalesOrderID = @Param
ORDER BY Style DESC;';  

-- Return handle for calling application
SELECT @P1;
GO

输出结果是 1 ，则该句柄为 1 

然后，应用程序在放弃已准备的计划之前，使用句柄值1执行查询两次。

EXEC sp_execute 1, 49879;  
GO

EXEC sp_execute 1, 48766;
GO

EXEC sp_unprepare 1; 
GO

 

【3】net代码调用sp_prepare

下面是一个.net代码调用sp_prepare的例子

SqlConnection con = new SqlConnection("server=stswordman6\sql2008r2_3;Trusted_Connection=True;");          

  con.Open(); 

SqlCommand cmd = con.CreateCommand(); 

cmd.CommandText = "exec sp_prepare @n output,N'@p1 int',N'select *From testtable where id=@p1'";

SqlParameter par=cmd.CreateParameter();

            par.SqlDbType = System.Data.SqlDbType.Int;

            par.ParameterName="@n";

            par.Direction= System.Data.ParameterDirection.Output;

            cmd.Parameters.Add(par);

            cmd.ExecuteNonQuery();

             cmd.CommandText = "exec sp_execute "+par.Value.ToString()+",1";

             SqlDataAdapter da = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();

            da.Fill(ds);

             Console.WriteLine(ds.Tables[0].Rows.Count);

            con.Close();