Creating a stored procedure

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