To create a stored procedure, you need to create a StoredProcedure object and specify what parameters and command text the stored procedure will consist of. The following code snippet creates a very simple stored procedure that will add a new record to the table that we created earlier:
1 Dim db As Database = sqlServer.Databases("MyNewDatabase")
2 Dim sp As New StoredProcedure(db, "MyNewStoredProcedure")
3
4 sp.TextMode = False
5 sp.TextBody = "INSERT INTO MyNewTable (TextField) VALUES ('Hello World')"
6 sp.Create()
You can of course create more complex stored procedures. For example we may want to create a similar stored procedure as above but instead of hard coding the value to be inserted into the table we want to define a parameter to be used instead. To do this, we need to create a new StoredProcedureParameter object and add it to the Parameters collection of the new StoredProcedure object:
1 Dim db As Database = sqlServer.Databases("MyNewDatabase")
2 Dim sp As New StoredProcedure(db, "MyNewStoredProcedure")
3
4 sp.TextMode = False
5 sp.Parameters.Add(New StoredProcedureParameter(sp, "@TextFieldValue", DataType.Char(25)))
6 sp.TextBody = "INSERT INTO MyNewTable (TextField) VALUES (@TextFieldValue)"
7
8 sp.Create()
Dropping Stored Procedures
To drop a stored procedure you call the Drop method of the StoredProcedure object you wish to delete:
1 sqlServer.Databases("MyNewDatabase").StoredProcedures("MyNewStoredProcedure").Drop()