Procedures Wizard (SQL Server)
Go Up to Microsoft SQL Server Object Wizards
Procedures are a reusable block of PL/SQL, stored in the database, that applications can call. Procedures streamline code development, debugging, and maintenance by being reusable. Procedures enhance database security by letting you write procedures granting users execution privileges to tables rather than letting them access tables directly.
The Procedure Wizard lets you:
- Name the procedure and specify its body.
- Specify any execution options and you can encrypt the stored procedure text in syscomments.
DBArtisan also supports natively-compiled stored procedures, which are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
To create a new procedure using a wizard:
- Open a creation wizard for a procedure. For details, see Opening an Object Wizard.
- Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
- Properties panel - for details, see Procedures (SQL Server) - Properties.
- Definition panel - for details, see Procedures (SQL Server) - Definition.
- Finally, use the Execute button to create the object.
Procedures (SQL Server) - Properties
When creating or editing a procedures, this tab/panel lets you work with the following settings:
Setting | Description |
---|---|
Owner |
Select the owner of the procedure. |
Name |
Provide a name for the procedure |
Procedure Number |
Optionally, provide a procedure number. By using a number, you can group procedures of the same name together. This also enables you to drop them using only one DROP PROCEDURE statement. So, the procedures bill;1, bill;2, bill;3, etc. will be dropped simultaneously when the time comes. |
Replication |
This option creates a procedure that is used as a stored procedure filter and is executed only during replication. |
Recompile |
The plan for this procedure will not be cached and the procedure is recompiled when it is run. This option is appropriate when you’re using atypical or temporary values and you don’t want to override the execution plan cached in memory. |
Encryption |
If you select this option, SQL Server will encrypt the syscomments table entry containing the text of the CREATE PROCEDURE statement. It keeps the procedure from being published as part of replication. |
Native Compilation |
This option sets natively-compiled stored procedures that allow for efficient query execution within the stored procedure. |
Procedures (SQL Server) - Definition
Complete the CREATE PROCEDURE outline provided by typing or pasting the body of the procedure.