《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()