Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  Data Programming  »  SQL Server  »  An introduction to SQL Server Management Objects  »  Creating and dropping stored procedures
An introduction to SQL Server Management Objects
by David Jeavons | Published  01/02/2007 | SQL Server | Rating:
Creating and dropping stored procedures

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()

Sponsored Links