SQL Server Stored Procedure basic syntax

From http://c21.cnblogs.com/archive/2006/05/08/393779.html

Concept of Stored Procedure
SQL Server provides a method to centralize some fixed operations by the SQL Server database Server to implement a task. This method is a stored procedure.
A stored procedure is a set of pre-compiled SQL statements and optional control flow statements. It is stored in a database and can be executed by an application through one call, it also allows users to declare variables, conditional executions, and other powerful programming functions.
SQL Server has two types of stored procedures: stored procedures provided by the system and custom stored procedures.

Stored procedures can be used for any purpose of using SQL statements. They have the following advantages:
You can execute a series of SQL statements in a single stored procedure.
You can reference other stored procedures from your stored procedures, which simplifies a series of complex statements.
The storage process is compiled on the server when it is created. Therefore, it runs faster than a single SQL statement and reduces the network communication burden.
Higher security.
Create a stored procedure

You can create a stored procedure in SQL Server in three ways:
① Use the create Stored Procedure Wizard to create a stored procedure.
② Create a stored procedure using SQL Server Enterprise Manager.
③ Use the create procedure command in the Transact-SQL statement to CREATE a stored PROCEDURE.

The following describes how to use the create procedure command in a Transact-SQL statement to CREATE a stored PROCEDURE.
Before creating a stored procedure, consider the following:
① The create procedure statement and other SQL statements cannot be combined into a single batch.
② Stored procedures can be nested, and the maximum depth of nesting cannot exceed 32 layers.
③ By default, the stored procedure creation permission belongs to the database owner, who can grant this permission to other users.
④ A stored procedure is a database object and its name must comply with the identifier rules.
⑤ You can only create a stored procedure in the current database.
⑥ The maximum size of a stored procedure is 128 M.

The syntax for using create procedure to CREATE a stored PROCEDURE is as follows:

QUOTE: create proc [EDURE] procedure_name [; number] [; number]
[{@ Parameter data_type}
[VARYING] [= default] [OUTPUT]
] [,... N]
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
[For replication]
AS SQL _statement [... n] syntax parameters used to CREATE a stored PROCEDURE with CREATE PROCEDURE are AS follows:

Procedure_name: Specifies the name of the stored procedure to be created.
Number: this parameter is an optional integer used to group stored procedures with the same name, so that the same group of processes can be removed with a drop procedure statement.
@ Parameter: parameters in the process. One or more parameters can be declared in the create procedure statement.
Data_type: Specifies the Data Type of a parameter.
VARYING: used to specify the result set supported by the OUTPUT parameter.
Default: Specifies the default value of a parameter.
Output: indicates that this parameter is a return parameter.

For example, create a simple Stored Procedure productinfo to retrieve product information.
Use northwind
If exists (Select name from sysobjects
Where name = 'productinfo' and type = 'P ')
Drop procedure productinfo
Go

Create procedure productinfo
As
Select * from products
Go
Execute the stored procedure using the following SQL statement: Execute productinfo
You can retrieve the product information. Execute the Stored Procedure

You can run the Execute Command to directly execute the stored procedure. The syntax format is as follows:
[[Exec [ute]
{[@ Return_status =]
{Procedure_name [; number] | @ procedure_name_var} [[@ parameter =] {value | @ variable [Output] | [Default]}
[,... N]
[With recompile]

Run the EXECUTE Command to pass a single parameter. It executes the showind stored procedure and takes titles as the parameter value. The showind stored procedure requires a parameter (@ tabname), which is the name of a table. The program list is as follows:
EXEC showind titles
Of course, variables can be explicitly named during execution:
EXEC showind @ tabname = titles
If this is the first statement in an isql script or batch processing, the EXEC statement can be omitted:
Showind titles or showind @ tabname = titles

The following example uses the default parameter
USE Northwind
GO
Create procedure insert_Products_1
(@ SupplierID_2 int,
@ CategoryID_3 int,
@ ProductName_1 nvarchar (40) = 'None ')
As insert into Products
(ProductName, SupplierID, CategoryID)
VALUES
(@ ProductName_1, @ SupplierID_2, @ CategoryID_3)
GO
Exec insert_Products_1 1, 1
Select * from Products where SupplierID = 1 and CategoryID = 1
GO

The following example uses response parameters
USE Northwind
GO
Create procedure query_products
(@ SupplierID_1 int,
@ ProductName_2 nvarchar (40) output)
AS
Select @ ProductName_2 = ProductName from products
Where SupplierID = @ SupplierID_1

Run the stored procedure to query the product name with SupplierID 1:
Declare @ product nvarchar (40)
Exec query_products 1, @ product output
Select 'product name' = @ product
Go

View stored procedures
After a stored procedure is created, its name is stored in the system table sysobjects, and its source code is stored in the system table syscomments. You can use the Enterprise Manager or system stored procedure to view the stored procedure you have created.

Use the Enterprise Manager to view the stored procedure created by the user

In Enterprise Manager, open the specified server and database items, select the database for which you want to create a stored procedure, and click the Stored Procedure folder. All stored procedures of the database are displayed in the right-side dialog box. Right-click the stored procedure you want to view and select the attribute option from the shortcut menu to view the source code of the stored procedure.

Use the system stored procedure to view the stored procedure created by the user

The system stored procedures and syntax are as follows:
Sp_help: displays the parameters and data types of stored procedures.
Sp_help [[@ objname =] name]
The parameter name is the name of the stored procedure to be viewed.
Sp_helptext: displays the source code of a stored procedure.
Sp_helptext [[@ objname =] name]
The parameter name is the name of the stored procedure to be viewed.
Sp_depends: used to display database objects related to stored procedures
Sp_depends [@ objname =] 'object'
The parameter object is the name of the stored procedure for viewing dependencies.
Sp_stored_procedures: Used to return the list of stored procedures in the current database

Modify Stored Procedure

The stored procedure can be changed based on user requirements or the definition of the base table. The alter procedure statement can be used to change the previously created PROCEDURE by executing the create procedure statement without changing the permissions or affecting the stored PROCEDURE or trigger. The syntax format is as follows:
ALTERPROC [EDURE] procedure_name [; number]
[{@ Parameterdata_type}
[VARYING] [= default] [OUTPUT] [,... n] [
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
[For replication]
AS
SQL _statement [... n]

Renaming and deleting stored procedures

1. Rename the Stored Procedure
To modify the name of a stored procedure, use the system stored procedure sp_rename. The syntax format is as follows:
Sp_rename original stored procedure name, new stored procedure name
You can also modify the name of a stored procedure through the Enterprise Manager.

Delete stored procedure

You can use the DROP command to delete stored procedures. The DROP command can delete one or more stored procedures or stored procedure groups from the current database. The syntax is as follows:
Drop procedure {procedure }[,... N]
Of course, you can also easily delete stored procedures using the Enterprise Manager.

Recompilation of Stored Procedures

After we use a stored procedure, we may have to add data columns to the table or add new indexes to the table for some reason, thus changing the logical structure of the database. In this case, you need to re-compile the stored procedure. SQL Server provides three methods to re-compile the stored procedure:
1. Set re-compilation when creating a stored procedure
Syntax format: create procedure procedure_name with recompile as SQL _statement
2. Set re-compilation when executing the Stored Procedure
Syntax format: EXECUTE procedure_name WITH RECOMPILE
3. Set re-compilation by using the system stored procedure
Syntax format: EXEC sp_recompile OBJECT

System stored procedures and Extended Stored Procedures

1. system stored procedures
The system stored procedures are stored in the master database and prefixed with sp _. They are mainly used to obtain information from the system table and help the system administrator manage SQL Server, it is convenient for users to view database objects. For example, sp_help, sp_helptext, and sp_helptext, which are used to view information about database objects.

2. extended storage process:
The extended stored procedure is prefixed with xp _. It is part of the open data service layer of the relational database engine. It enables users to dynamically connect to the library (DLL) the functions contained in the file implement the logic, and thus extend the functions of the Transact-SQL statement. You can call these functions from the Transact-SQL statement as you call the Transact-SQL process.
For example, the stored procedure xp_cmdshell is used to execute a specified command string for an operating system shell and return any output as text.
Run the Code:
Use master
Exec xp_cmdshell 'dir *. exe'
The execution result returns the text information of the file in the system directory.

Finally, we will give you an example:

QUOTE :/**
1. Create a stored procedure with query parameters in the northwind database,
When you enter a total order amount @ total, you must query the orders that exceed this value.
Information about the product, including the product name, supplier name, unit quantity,
The unit price, and the total order amount of the product, which are returned using an output parameter.
Number of products that meet the query Conditions
**/

IF exists (select * from SysObjects where name = 'more _ than_total 'and type = 'P ')
Drop procedure more_than_total
Go
Create procedure More_Than_Total
@ Total money = 0
AS
Declare @ amount smallint
BEGIN
Select distinct
P. productName,
S. contactName,
P. UnitPrice

From Products P inner join [order Details] O
On p. productID = o. productID inner join suppliers s
On p. supplierID = s. SupplierID
Where O. productID in
(Select productID
From [order Details]
Group by productId
Having sum (quantity * unitprice)> @ total
)
END
GO