Previous Topic: Define SQL Server SchemasNext Topic: Define SQL Server Synonyms


Define SQL Server Stored Procedures

A stored procedure is a pre-compiled collection of SQL statements that are stored under a name and processed as a unit. The SQL Server Stored Procedure Editor simplifies the management of database-level and table-level stored procedures. The Code type determines if the stored procedure is intended to be a single procedure, generated for the entire database, or a set of procedures, generated once for each associated table.

The order in which you generate stored procedures to your database is important. The stored procedure code is generated according to the order in the SQL Server Stored Procedures Editor.

To define SQL Server stored procedures

  1. Click Stored Procedures on the Model menu.

    The SQL Server Stored Procedure Editor opens.

  2. Select the stored procedure in the Navigation Grid that you want to define and work with the following options:

    Note: Click New New icon in property editors to create a new object on the toolbar to create a new stored procedure. Use the Enter filter text box to filter a very large list of stored procedures to quickly locate the one that you want to define.

    Name

    Specifies the name of the stored procedure. Change the name of the stored procedure in this field.

    Schema

    Specifies the schema of the database to which the stored procedure belongs. Select the schema from the drop-down list.

    Type

    Specifies whether the stored procedure type is model-level or table-level. Select the type from the drop-down list.

    Enabled

    Specifies to enable the stored procedure in the database.

    Note: The Stored Procedure Browser icon Macro/Stored Procedure Browser Icon, which opens the SQL Server Procedure Browser, is only enabled for table-level stored procedures. The SQL Server Procedure Browser lets you manage the tables and views that generate a table-level stored procedure.

    Attach To New

    Specifies to attach the selected stored procedure to each new table that you create. Clear the check box if you want to manually attach the stored procedure to new tables.

    Generate

    Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

    Generate As Pre-Script

    Specifies whether the stored procedure is generated as part of a pre-script. Select an option from the drop-down list.

  3. Click the General tab and work with the following options:
    'Stored Procedure Name' Code Type

    Specifies the code type of the stored procedure and its associated values. Select one of the following code types from the drop-down list:

    SQL

    Specifies to use SQL code. Work with the following options when you select the SQL code type:

    For Replication

    Specifies the stored procedure is invoked as part of database replication.

    Encryption

    Specifies that the original text of the procedure code is encrypted in the database.

    Recompile

    Specifies that the SQL Server database not store a plan for the stored procedure and that the stored procedure is compiled at runtime.

    Startup

    Specifies to execute the stored procedure upon startup of SQL Server.

    Assembly

    Specifies to use Common Language Runtime code defined in an assembly. Work with the following option when you select the Assembly code type:

    Startup

    Specifies to execute the stored procedure upon startup of SQL Server.

    NativeCompilation

    Specifies to use stored procedures that are compiled to native code that access memory-optimized tables. Native compilation is available SQL Server 2014 onward.

    Transaction Isolation Level

    Specifies the the level of isolation to be followed by the transaction.

    Language

    Specifies the programming language which the procedure or function is written in.

    Date First

    Specifies the first date of a week of natively complied stored procedure atomic block.

    Date Format

    Specifies the date format of natively complied stored procedure atomic block.

    Delayed Durability

    Specifies whether the stored procedure uses delayed durability.

    Type

    Specifies the permission level in effect when the stored procedure is executed.

    Select User

    Specifies the user whose permissions are in effect for the execution of the procedure.

    Note: This option is only available if you have selected the Execute As User option in the Type drop-down list.

    'Stored Procedure Name' Parameter_Order_List

    Specifies the parameters for the stored procedure and lets you select the order in which the parameters execute and the properties of each one.

    Note: Click New New icon in property editors to create a new object on the toolbar to add a new parameter, or click Delete property editor DELETE button to delete the selected parameter.

  4. Work with the other tabs in the editor to access additional design features, for example:
  5. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
  6. (Optional) Click the UDP tab to work with user-defined properties for the object.
  7. (Optional) Click the Notes tab to view and edit user notes.
  8. Click Close.

    The stored procedure is defined and the SQL Server Stored Procedure Editor closes.

More information:

Define the Code Type for an Object in a SQL Server Property Editor

View Expanded Code in a Property Editor

Change the Object Creation Order in a Property Editor

Define Permissions for an Object in a SQL Server Property Editor

Add a Comment in a Property Editor

Add a UDP in a SQL Server Physical Property Editor

SQL Server Browser for Functions, Script Templates, and Stored Procedures