Creating a stored procedure

This recipe shows how to create an encrypted stored procedure using SMO and PowerShell.

Getting ready

The T-SQL equivalent of the encrypted stored procedure we are about to recreate in PowerShell is as follows:

CREATE PROCEDURE [dbo].[uspGetPersonByLastName]
@LastName [varchar](50)
WITH ENCRYPTION
AS

SELECT
   TOP 10
   BusinessEntityID,
   LastName
FROM
   Person.Person
WHERE
   LastName = @LastName

How to do it...

Follow these steps to create the uspGetPersonByLastName stored procedure using PowerShell:

1. Open PowerShell ISE as administrator.

2. Import the SQLPS module and create a new SMO Server Object:

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

   Add the following script and run:

   $dbName = "AdventureWorks2014"
   $db = $server.Databases[$dbName]
   
   #storedProcedure class on MSDN:
   #http:/...