《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]

WITH ENCRYPTION

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)

否则，如果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()

【云原生】SQL（及存储过程）跑得太慢怎么办？ SQL作为目前最常用的数据处理语言，广泛应用于查询、跑批等场景。当数据量较大时，使用SQL（以及存储过程）经常会发生跑得很慢的情况，这就要去优化SQL。
【Sql Server】存储过程通过定时执行添加记录作业 通过上篇了解了什么是存储过程，创建存储过程的方法，以及调用存储过程的方法 本次将通过数据库中的作业功能，进行定时执行存储过程，这样就可以完成我们刚开始假设的场景
【Sql Server】存储过程通过作业定时执行按天统计记录 通过前两篇文章的学习，我们已经对创建表、存储过程、作业等功能点有所了解 本次将结合前面所学习的知识点，创建统计表以及结合作业定时按天以及实时统计域名各个长度的记录值
【Sql Server】存储过程的创建和调用，随机添加域名记录 假设有这样一个场景 创建一个储存过程A，它执行添加一条随机产生3到8位长度的域名记录，通过定时器T1每隔1秒执行一次存储过程A 创建另一个存储过程B，它执行统计域名的长度3到8的记录数，通过定时器T2每隔1秒执行一次存储过程B
异步社区 异步社区(www.epubit.com)是人民邮电出版社旗下IT专业图书旗舰社区，也是国内领先的IT专业图书社区，致力于优质学习内容的出版和分享，实现了纸书电子书的同步上架，于2015年8月上线运营。公众号【异步图书】，每日赠送异步新书。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载