《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》——2.14　创建存储过程

本节书摘来自异步社区出版社《PowerShell V3—SQL Server 2012数据库自动化运维权威指南》一书中的第2章，第2.14节，作者：【加拿大】Donabel Santos，更多章节内容可以访问云栖社区“异步社区”公众号查看。

2.14　创建存储过程

本方案展示了如何用PowerShell和SMO创建加密的存储过程。

2.14.1　准备

我们要用PowerShell创建的加密存储过程的等价T-SQL代码如下：

CREATE PROCEDURE [dbo].[uspGetPersonByLastName] @LastName [varchar]
(50)
WITH ENCRYPTION
AS
SELECT 
　　TOP 10 
　　BusinessEntityID,
　　LastName
FROM 
　　Person.Person
WHERE 
　　LastName = @LastName

2.14.2　如何做…

按照如下步骤使用PowerShell创建存储过程uspGetPersonByLastName。

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．导入SQLPS模块，创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3．添加如下脚本并运行。

$dbName = "AdventureWorks2008R2"
$db = $server.Databases[$dbName]
#storedProcedure class on MSDN: 
#http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.storedproc 
edure.aspx
$sprocName = "uspGetPersonByLastName"
$sproc = $db.StoredProcedures[$sprocName]
#if stored procedure exists, drop it
if ($sproc)
{
　　　$sproc.Drop()
}
$sproc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure
-ArgumentList $db, $sprocName
#TextMode = false means stored procedure header 
#is not editable as text
#otherwise our text will contain the CREATE PROC block
$sproc.TextMode = $false
$sproc.IsEncrypted = $true
$paramtype = [Microsoft.SqlServer.Management.SMO.Datatype]::VarChar(50);
$param = New-Object –TypeName
Microsoft.SqlServer.Management.SMO.StoredProcedureParameter –ArgumentList
$sproc,"@LastName",$paramtype
$sproc.Parameters.Add($param)
#Set the TextBody property to define the stored procedure. 
$sproc.TextBody =　@" 
SELECT 
　　TOP 10 
　　BusinessEntityID,
　　LastName
FROM 
　　Person.Person
WHERE 
　　LastName = @LastName
"@

# Create the stored procedure on the instance of SQL Server. 
$sproc.Create()
#if later on you need to change properties, can use the Alter method

4．检查是否存储过程已创建。

（1）打开SSMS。

（2）展开AdventureWorks2008R2数据库。

（3）展开“Programmability | Stored Procedures”。

（4）查看存储过程是否在这里。

5．在PowerShell中测试存储过程。在同一个会话中，输入如下代码并运行。

$lastName = "Abercrombie"
$result = Invoke-Sqlcmd `
-Query "EXEC uspGetPersonByLastName @LastName=`'$LastName`'" `
-ServerInstance "$instanceName" `
-Database $dbName 
$result | Format-Table –AutoSize

2.14.3　如何实现…

为了创建存储过程，首先需要初始化SMO StoredProcedure对象。创建这个对象时，需要传递数据库句柄和存储过程名作为参数。

$sproc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure
-ArgumentList $db, $sprocName

你可以设置存储过程对象的一些属性，如是否加密。

$sproc.IsEncrypted = $true

如果定制了TextMode = $true，你需要自己创建存储过程的头部。如果你有参数，这些将会在你的文本头部被定义，例如：

$sproc.TextMode = $true
$sproc.TextHeader = @"
CREATE PROCEDURE [dbo].[uspGetPersonByLastName]
 @LastName [varchar](50)
AS
"@

否则，如果TextMode = $false，技术上允许PowerShell自动生成头部，基于你给定的其他属性和参数设置。你也需要逐一创建参数对象并将它们添加到存储过程中。

$sproc.TextMode = $false
$paramtype = [Microsoft.SqlServer.Management.SMO.
Datatype]::VarChar(50);
$param = New-Object -TypeName Microsoft.SqlServer.Management.SMO.
StoredProcedureParameter -ArgumentList $sproc,"@LastName",$paramtype
$sproc.Parameters.Add($param)

当创建存储过程时，使用字符串设置存储过程对象的TextBody属性定义。

$sproc.TextBody =　@" 
SELECT 
　　TOP 10 
　　BusinessEntityID,
　　LastName
FROM 
　　Person.Person
WHERE 
　　LastName = @LastName
"@

一旦头部信息、定义和存储过程属性就位，你可以调用Create方法，将CREATEPROC语句发送到SQL Server，并创建存储过程。

# Create the stored procedure on the instance of SQL Server. 
$sproc.Create()