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:
#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:
#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.
