SMO - Stored procedures

The StoredProcedure object represents SQL Server Stored Procedures.

Enumerating Stored Procedures
Stored Procedures are represented by the StoredProcedures property of the Database object. The following example demonstrates how to enumerate Stored Procedures of a selected database (in this case, AdventureWorks).

Server srv = new Server(conn);
Database db = srv.Databases["AdventureWorks"];
foreach (StoredProcedure sp in db.StoredProcedures)
{
    /*
     *  SOME CODE
     */ 
}

Creating a Stored Procedure
When you want to create a Stored Procedure, the TextBody property must be set to the T-SQL script that defines the Stored Procedure. Sometimes developers create a Stored Procedure with parameters. Parameters of the Stored Procedure are represented by the Parameters property of the StoredProcedure object. The Parameters property is a collection of StoredProcedureParameter objects. All parameters require the "@" prefix in the Stored Procedure parameter name.

Database myNewDatabase = srv.Databases["myNewDatabase"];
StoredProcedure sp = new StoredProcedure(myNewDatabase, "spRowByID_Get");
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
StoredProcedureParameter param;
param = new StoredProcedureParameter(sp, "@ID", DataType.Int);
sp.Parameters.Add(param);
string spBody = "SELECT * FROM MyFirstSMOTable WHERE ID=@ID";
sp.TextBody = spBody;
sp.Create();

In this example, a Stored Procedure named spRowByID_Get was created with an input parameter @ID. When you want to alter an existing Stored Procedure, just create a StoredProcedure object, set all the properties you need to change, and call the Alter() method.

Database myNewDatabase = srv.Databases["myNewDatabase"];
StoredProcedure sp = myNewDatabase.StoredProcedures["spRowByID_Get"];
/*
 *  stored procedure modifications
 */ 
sp.Alter();

When you want to drop an existing Stored Procedure, just call the Drop() method of the StoredProcedure object.