http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/02/18/a-month-of-powershell-day-18-databases-stored-procedures/

Printed 2014/11/23 12:01PM

A Month of PowerShell – Day 18 (Databases: Stored Procedures)

By Wayne Sheffield, 2013/02/18

Welcome to Day 18 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Continuing the mini-series of objects compiled with T-SQL code, today we will work with stored procedures.

Creating a stored procedure

What would you think the difference is between creating a view and a stored procedure? For a simple stored procedure (without parameters), it’s just changing the class to StoredProcedure:

Source code   
#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$ObjectName = "spTestTable"
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# assign the database name to a variable
$MyDB = $Server.Databases[$DBName]
 
# assign the schema to a variable
$Schema = $MyDB.Schemas[$SchemaName]
 
# check to see if the stored procedure exists
$Object = $MyDB.StoredProcedures.Item($ObjectName, $SchemaName)
 
IF (!($Object))
{
    $Object = New-Object ("$SMO.StoredProcedure") ($MyDB, $ObjectName, $SchemaName)
    $Object.TextHeader = "CREATE PROCEDURE [$SchemaName].[$ObjectName] AS"
    $Object.TextBody = 'SELECT * FROM Common.TestTable ORDER BY RowGuid;'
    $Object.Create()
}

Creating a stored procedure with parameters

Creating a stored procedure with parameters changes the way that you need to create the procedure – specifically you need to specify the parameters. So, let’s create a procedure with both input and output parameters:

Source code   
#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
$SchemaName = "Common"
$ObjectName = "spTestTable2"
 
#Assign various data types to variables
$dtDateTime = [Microsoft.SqlServer.Management.Smo.Datatype]::DateTime
$dtUniqueI  = [Microsoft.SqlServer.Management.Smo.Datatype]::UniqueIdentifier
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# assign the database name to a variable
$MyDB = $Server.Databases[$DBName]
 
# assign the schema to a variable
$Schema = $MyDB.Schemas[$SchemaName]
 
# check to see if the stored procedure exists
$Object = $MyDB.StoredProcedures.Item($ObjectName, $SchemaName)
 
IF (!($Object))
{
    $Object = New-Object ("$SMO.StoredProcedure") ($MyDB, $ObjectName, $SchemaName)
    $Object.TextMode = $false #TextHeader created from supplied parameters/names
    #Add a parameter
    $Param = New-Object ("$SMO.StoredProcedureParameter") ($Object, '@RowGuid', $dtUniqueI)
    $Object.Parameters.Add($Param)
    #Add an output parameter
    $Param = New-Object ("$SMO.StoredProcedureParameter") ($Object, '@LastUpdatedDT', $dtDateTime)
    $Param.IsOutputParameter = $true
    $Object.Parameters.Add($Param)
    $Object.TextBody = @'
SELECT @LastUpdatedDT =
    (SELECT  LastUpdatedDT
     FROM    Common.TestTable
     WHERE   RowGuid = @RowGuid
    );
'@
    $Object.Create()
}

Altering and dropping stored procedures

Altering and dropping stored procedures are similar to what we did yesterday when altering and dropping views. Verify that the procedure does exist, and set the new procedure code in the TextBody parameter. If necessary, add / drop parameters, then call the procedures ALTER method. To drop, just call the DROP method after verifying that the procedure exists.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.