MS SQL Basics Tutorial: Creating Stored Procedures

In MS SQL Server 2000, there are two ways to create a stored procedure: one is to use the Transaction-sql command create Procedure, and the other is to use the graphical management tool Enterprise Manager. Creating stored procedures with Transaction-sql is a faster approach, but for beginners it is easier to understand and use enterprise Manager.

When creating a stored procedure, you need to determine the three components of the stored procedure;

All input parameters and the output parameters passed to the caller. An action statement executed against the database, including statements that invoke other stored procedures, and a state value returned to the caller to indicate whether the call succeeded or failed. 12.2.1 Use Enterprise Manager to create a stored procedure

Follow the steps below to create a stored procedure with enterprise Manager:

Start Enterprise Manager and log on to the server you want to use. Select the database where you want to create the stored procedure, click the stored Procedure folder in the left pane, and then display all stored procedures for that database in the right pane, as shown in Figure 12-1. Right-click the Stored Procedure folder, select New Stored Procedure in the pop-up menu, and open the Create Stored Procedure dialog box, as shown in Figure 12-2.

Enter the body of the stored procedure. Click Check Syntax to verify that the syntax is correct. Click OK to save. In the right pane, right-click the stored procedure, select the all task in the pop-up menu, and select

Managepermissions, set permissions, as shown in Figure 12-3.

12.2.2 creating a stored procedure with the Create PROCEDURE command

You can create stored procedures by using the Create Procedure command, and before you create a stored procedure, you should consider the following:

In a batch, the Create PROCEDURE statement cannot be merged with other SQL statements; The database owner has the default permission to create a stored procedure that can pass that permission to another user; The stored procedure must conform to the naming rules as a database object; Only stored procedures that belong to the current database can be created in the current database. The syntax rules for creating stored procedures with Create Procedure are as follows:

CREATE PROC [edure] procedure_name [; number]

[{@parameter data_type}

[Varying] [= default] [OUTPUT]

] [,... N]

[With

{RECOMPILE | Encryption | RECOMPILE, encryption}]

[For REPLICATION]

As sql_statement [... n]