开发者社区> 异步社区> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

《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)查看存储过程是否在这里。

image

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server 优化存储过程的七种方法
原文: SQL Server 优化存储过程的七种方法 优化存储过程有很多种方法,下面介绍最常用的7种。 1.使用SET NOCOUNT ON选项 我们使用SELECT语句时,除了返回对应的结果集外,还会返回相应的影响行数。
1137 0
一条SQL语句获取具有父子关系的分类列表(mysql)
有如下表数据: 获取“菜单”分类的子分类数据列表: SELECT a.cat_id, a.cat_name, a.sort_order AS parent_order, a.cat_id, b.
2286 0
T-SQL查询:语句执行顺序
原文:T-SQL查询:语句执行顺序 读书笔记:《Microsoft SQL Server 2008技术内幕:T-SQL查询》   ===============  T-SQL查询的执行顺序 ===============      =============== T-SQL查询的示意图...
789 0
sql语句延时执行或者是指定时间执行
--使用waitfor语句延迟或暂停程序的执行 --waitfor{delay'time'|time 'time'} delay是指间隔时间 最长到24小时 time是指定时间执行   waitfor delay '00:00:10' --延迟10秒 A. 使用 WAITFOR TIME 以下示例在晚上 10:20 (22:20) 执行存储过程 sp_update_job。
943 0
JAVA中处理事务的程序--多条更新SQL语句的执行(包括回滚) .
转自:http://blog.csdn.net/fafeiboy/article/details/2734889  在与数据库操作时,如果执行多条更新的SQL语句(如:update或insert语句),在执行第一条后如果出现异常或电脑断电, 则后面的SQL语句执行不了,这时候设定我们自己提交SQL语句,不让JDBC自动提交,格式为: conn.
1061 0
[20121101]tkprof抽取sql语句.txt
[20121101]tkprof抽取sql语句.txt 有时候跟踪分析sql语句,并不是要看里面的递归的执行,或者性能问题.而是要收集跟踪命令的执行序列. 查看tkprof的帮助: Usage: tkprof tracefile outputfile [expl...
771 0
SQL语句整理
1、问题贴见(这里一个注意点为:coalesce(字段,0) 使用,即判断这个字段是否为NULL,如果为NULL,则为0) http://topic.
625 0
SQL Server功能性语句
1 注释符注释是指程序代码中不执行的文本字符串,是对程序的说明,可以提高程序的可读性,使程序代码更易于维护,一般嵌入在程序中并以特殊的标记显示出来。在Transact-SQL中,注释可以包含在批处理、存储过程、触发器中,有两种类型的注释符:  --:这是ANSI标准的两个连字符组成的注释符,用于单行注释。
628 0
+关注
异步社区
异步社区(www.epubit.com)是人民邮电出版社旗下IT专业图书旗舰社区,也是国内领先的IT专业图书社区,致力于优质学习内容的出版和分享,实现了纸书电子书的同步上架,于2015年8月上线运营。公众号【异步图书】,每日赠送异步新书。
12049
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载