Compartir a través de

StoredProcedures Collection

  • Artículo

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

The StoredProcedures collection contains StoredProcedure objects that reference the system and user-defined stored procedures of a Microsoft SQL Server database.

Modelo de objeto SQL-DMO con el objeto actual

Properties

Count Property

 

Methods

Add Method

Refresh Method

Item Method

Remove Method (Collections)

ItemByID Method

 

Notas

With the StoredProcedures collection, you can:

  • Create a stored procedure.
  • Remove a stored procedure.

For more information about creating stored procedures using the StoredProcedure object and StoredProcedures collection, see the StoredProcedure Object section.

To remove a stored procedure

  1. Get the StoredProcedure object referencing the targeted stored procedure from the StoredProcedures collection using the Item or ItemByID method. When extracting a StoredProcedure object using the name of the referenced stored procedure, use the owner name to qualify the name, as in:

    Set oStoredProcedure = _
oDatabase.StoredProcedures("[Sales By Year]", "dbo")

  2. Use the Remove method of the StoredProcedure object to remove the targeted stored procedure.

When using the Item or Remove method, the StoredProcedures collection supports member identification using either name or ordinal reference syntax. For example:

Set oStoredProcedure = _
oDatabase.StoredProcedures("[Ten Most Expensive Products]")

Or:

Set oStoredProcedure = oDatabase.StoredProcedures(1)

Additionally, when using name-based item selection, the Item method allows owner name qualification of the targeted SQL Server stored procedure as shown earlier. When using the Remove method, the StoredProcedures collection does not support qualification of targeted object by owner name. It is suggested that you use the Item method to extract the target, and then use the Remove method of the StoredProcedure object to drop a stored procedure.

[!NOTA] Creating or removing SQL Server stored procedures by using the StoredProcedures collection requires appropriate privilege. The SQL Server login used for SQLServer object connection must be a member of the fixed role db_ddladmin or a role with greater privilege.