Creating Stored Procedures

You create stored procedures using the CREATE PROCEDURE statement. Permission to execute CREATE PROCEDURE defaults to the database owner, who can transfer it to other users. Stored procedures are database objects, and their names must follow the rules for identifiers. You can create a stored procedure only in the current database.

With the exception of CREATE statements, any number and type of SQL statements can be included in stored procedures. (For more information about the rules for creating stored procedures, see Stored Procedure Rules.)

You can nest stored procedures (have one stored procedure call another) up to 16 levels. The nesting level increases by one when the called procedure begins execution and decreases by one when the called procedure completes execution. Attempting to exceed the maximum of 16 levels of nesting causes the whole calling procedure chain to fail. The current nesting level is stored in the @@NESTLEVEL global variable.

After procedures are created, they are executed.

    To create a stored procedure

• From the Manage menu in SQL Enterprise Manager, choose Stored Procedures, and then choose New from the Procedures drop-down list.

  (For details, search for "stored procedures" in SQL Enterprise Manager Help.)

Or

• Use the CREATE PROCEDURE statement.

  For example:

  CREATE PROCEDURE namelist
  AS SELECT name FROM sysusers

  lists the names of all the users in a database. You get a message stating that the command did not return any data, and it did not return any rows. This is fine. The procedure has been created.

  (For details, see the CREATE PROCEDURE statement in the Microsoft SQL Server Transact-SQL Reference.)