MS SQL Basics Tutorial: Creating Stored Procedures

Source: Internet
Author: User

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]

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.