SQL server 2005 data storage process learning Notes

SQL server 2005 stored procedure syntax

CREATE {PROC | PROCEDURE} [schema_name.] procedure_name [; number]
[{@ Parameter [type_schema_name.] data_type}
SQL server 2005 stored procedure

Syntax

CREATE {PROC | PROCEDURE} [schema_name.] procedure_name [; number]
[{@ Parameter [type_schema_name.] data_type}
[VARYING] [= default] [OUT | OUTPUT] [READONLY]
] [,... N]
[WITH <procedure_option> [,... n]
[For replication]
AS {<SQL _statement> [;] [... n] | <method_specifier>}
[;]
<Procedure_option >::=
[ENCRYPTION]
[RECOMPILE]
[Execute as Clause]

<SQL _statement >::=
{[BEGIN] statements [END]}

<Method_specifier >::=

External name assembly_name.class_name.method_name

SQL Stored procedure creation method

-- Create a data table
6> create table employee (
7> id integer not null primary key,
8> first_name VARCHAR (10 ),
9> last_name VARCHAR (10 ),
10> salary DECIMAL (10, 2 ),
11> start_Date DATETIME,
12> region VARCHAR (10 ),
13> city VARCHAR (20 ),
14> managerid INTEGER
15> );
16> GO
1> insert into employee VALUES (1, 'Jason ', 'Martin', 5890, '2017-03-22 ', 'North', 'Vancouver', 3 );
2> GO

(1 rows affected)
1> insert into employee VALUES (2, 'Alison ', 'mathews', 4789, '2017-07-21 ', 'south', 'utown', 4 );
2> GO

(1 rows affected)
1> insert into employee VALUES (3, 'James ', 'Smith', 6678, '2017-12-01 ', 'North', 'Paris', 5 );
2> GO

(1 rows affected)
1> insert into employee VALUES (4, 'cela', 'Rice ', 5567, '2017-03-03', 'south', 'London, 6 );
2> GO

(1 rows affected)
1> insert into employee VALUES (5, 'Robert ', 'black', 4467, '2017-07-02', 'east', 'Newton ', 7 );
2> GO

(1 rows affected)
1> insert into employee VALUES (6, 'Linda ', 'green', 6456, '2017-05-19', 'east', 'Calgary ', 8 );
2> GO

(1 rows affected)
1> insert into employee VALUES (7, 'David ', 'Larry', 5345, '2017-03-18 ', 'West', 'New York', 9 );
2> GO

(1 rows affected)
1> insert into employee VALUES (8, 'James ', 'cat', 4234, '2017-07-17', 'West', 'Regina ', 9 );
2> GO

(1 rows affected)
1> insert into employee VALUES (9, 'job', 'Act ', 6123, '2017-04-16', 'North', 'Toronto ', 10 );
2> GO

(1 rows affected)
1>
2> select * from employee;
3> GO
Id first_name last_name salary start_Date region city managerid
-----------------------------------------------------------------------------------------------------------
1 Jason Martin 5890.00 00:00:00. 000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00. 000 South Utown 4
3 James Smith 6678.00 00:00:00. 000 North Paris 5
4 Celia Rice 5567.00 00:00:00. 000 South London 6
5 Robert Black 4467.00 00:00:00. 000 East Newton 7
6 Linda Green 6456.00 00:00:00. 000 East Calgary 8
7 David Larry 5345.00 00:00:00. 000 West New York 9
8 James Cat 4234.00 00:00:00. 000 West Regina 9
9 Joan Act 6123.00 00:00:00. 000 North Toronto 10

(9 rows affected)

-- Create an SQL storage tutorial
3> create procedure spInsertOrUpdateEmployee
4> @ FirstName nVarChar (50 ),
5> @ LastName nVarChar (25 ),
6> @ Salary Money
7>
8> if exists (SELECT * From Employee Where First_name = @ FirstName)
9> UPDATE Employee SET Last_NAME = @ LastName, Salary = @ Salary
10> WHERE First_name = @ FirstName
11> ELSE
12> insert into Employee (ID, First_Name, Last_Name, Salary)
13> SELECT 99, @ FirstName, @ LastName, @ Salary
14> GO

-- Delete a table
5> drop table employee;
6> GO

Schema_name

The name of the architecture to which the process belongs.

Procedure_name

Name of the new stored procedure. The process name must follow the rules of the identifier and be unique in the architecture.

We strongly recommend that you do not use the prefix sp _ in the process name _. This prefix is used by SQL Server to specify system stored procedures. For more information, see create a stored procedure (database engine ).

You can use a numerical symbol (#) (# procedure_name) before procedure_name to create a local temporary process. You can use two numerical symbols (# procedure_name) to create a global temporary process. You cannot specify a temporary name for a CLR stored procedure.

The complete name (including #) of a stored procedure or global temporary stored procedure cannot exceed 128 characters. The complete name (including #) of the local temporary stored procedure cannot exceed 116 characters.

; Number

Is an optional integer used to group processes with the same name. You can use a drop procedure statement to delete these grouping processes together. For example, an application called orders may use a process named orderproc; 1, orderproc; 2, and so on. The drop procedure orderproc statement deletes the entire group. If the name contains a separator identifier, the number should not be included in the identifier; only appropriate separators should be used before and after procedure_name.

Numbered stored procedures have the following restrictions:

Xml or CLR user-defined types cannot be used as data types.

You cannot create a scheduler guide for a numbered stored procedure.

Note:
This function will be deleted in later versions of Microsoft SQL Server. Avoid using this function in new development work, and start to modify the application that is currently using this function.

@ Parameter
Parameters in the process. One or more parameters can be declared in the create procedure statement. Unless the default value of the parameter is defined or the parameter is set to equal to another parameter, you must provide a value for each declared parameter during the call process. A stored procedure can have a maximum of 2,100 parameters. If the process contains a table value parameter and this parameter is missing in the call, the default value of the empty table is input.

Use the at symbol (@) as the first character to specify the parameter name. The parameter name must comply with the identifier rules. The parameters of each process are only used for this process. The same parameter name can be used in other processes. By default, a parameter can only replace a constant expression, rather than a table name, column name, or other database object name. For more information, see EXECUTE (Transact-SQL ).

If for replication is specified, the parameter cannot be declared.

[Type_schema_name.] data_type
Parameters and the data type of the architecture. All data types can be used as parameters in the stored procedure of Transact-SQL. You can use a user-defined table type to declare a table value parameter as a parameter in the stored procedure of Transact-SQL. Only table value parameters can be specified as input parameters, which must contain the READONLY keyword. The cursor data type can only be used for OUTPUT parameters. If the cursor data type is specified, the VARYING and OUTPUT keywords must also be specified. You can specify multiple output parameters for the cursor data type.

For CLR stored procedures, char, varchar, text, ntext, image, cursor, user-defined table type, and table cannot be specified as parameters. For more information about the relationship between CLR types and SQL Server data types, see CLR ing CLR parameter data. For more information about SQL Server System data types and syntaxes, see data types (Transact-SQL ).

If the data type of a parameter is a CLR user-defined type, EXECUTE permission must be granted to the parameter.

If type_schema_name is not specified, the SQL Server database engine references type_name in the following order:

The data type of the SQL Server system.

The default architecture of the current user in the current database.

Dbo architecture in the current database.

For stored procedures with numbers, the data type cannot be xml or CLR user-defined type.

VARYING
Specify the result set supported by the output parameter. This parameter is dynamically constructed by the stored procedure and its content may change. Only applicable to cursor parameters.

Default
The default value of the parameter. If the default value is defined, the process can be executed without specifying the value of this parameter. The default value must be a constant or NULL. If the process uses a parameter with the LIKE keyword, it can contain the following wildcards: %, _, [], and [^].

Note:
Only the default value of the CLR process is recorded in the sys. parameters. default column. This column is NULL for the Transact-SQL process parameter.

OUTPUT
Indicates that the parameter is an output parameter. The value of this option can be returned to the statement that calls EXECUTE. Use the OUTPUT parameter to return the value to the caller of the process. Unless it is a CLR process, the text, ntext, and image parameters cannot be used as OUTPUT parameters. The OUTPUT parameter using the OUTPUT keyword can be a placeholder for the cursor, except for the CLR process. The user-defined table type cannot be specified as the OUTPUT parameter of the stored procedure.

READONLY
Indicates that parameters cannot be updated or modified in the body of the process. If the parameter type is user-defined table type, you must specify READONLY.

RECOMPILE
Indicates that the database engine does not cache the plan of the process and the process is compiled at runtime. If for replication is specified, this option cannot be used. RECOMPILE cannot be specified for CLR stored procedures.

To instruct the database engine to discard the plan for a single query in the stored procedure, use the RECOMPILE query prompt. For more information, see the query prompt (Transact-SQL ). If the atypical value or temporary value is used only for a subset of the stored procedure query, use the RECOMPILE query prompt.

ENCRYPTION
Indicates that SQL Server converts the original text of the CREATE PROCEDURE statement to a fuzzy format. Fuzzy code output cannot be directly displayed in any directory view of SQL Server. Users who do not have access to system tables or database files cannot retrieve fuzzy text. However, privileged users who can access system tables through the DAC port or who can directly access data files can use this text. In addition, users who can append a debugger to server processes can retrieve decrypted processes from the memory at runtime. For more information about accessing system metadata, see metadata visibility configuration.

This option is invalid for CLR stored procedures.

The creation process using this option cannot be released during SQL Server replication.

EXECUTE
Specifies the security context in which the stored procedure is executed.

For more information, see The execute as clause (Transact-SQL ).

FOR REPLICATION
The storage process created for replication cannot be executed on the subscription server. Stored procedures created using the for replication option can be used as stored procedure filters and can only be executed during the REPLICATION process. If for replication is specified, the parameter cannot be declared. For clr stored procedures, you cannot specify for replication. Ignore the RECOMPILE option FOR the process of using for replication to create.

The for replication process will include the RF object type in sys. objects and sys. procedures.

<SQL _statement>
One or more Transact-SQL statements to be included in the process. For more information about some applicable restrictions, see the remarks section.

External name assembly_name.class_name.method_name
Specifies the method of the. NET Framework assembly for CLR stored procedure reference. Class_name must be a valid SQL Server identifier and the class must exist in the dataset. If the class contains a period (.) to separate the names of the specified namespaces for each part of the namespace, you must use square brackets ([]) or quotation marks ("") to separate the class names. The specified method must be a static method of this class.

Note:

By default, SQL Server cannot execute CLR code. You can create, modify, and delete database objects that reference the Common Language Runtime module. However, these references can be executed in SQL Server only after the clr enabled option is enabled. To enable this option, use sp_configure.

 
-- Call the system stored procedure xp_mongoshell
Exec xp_cmdshell 'MD d:/temp ', output

Create proc test
@ A int, @ B int
As
Select * from T_Person

Go
Exec test

-- All parameters are input parameters.
If object_id ('test', 'P') is not null
Drop proc test
Create proc test
@ A int, @ B int
As
Select @ a + @ B

Exec test @ B = 2, @ a = 12

-- Parameters with output parameters
If object_id ('test', 'P') is not null
Drop proc test
Create proc test
@ A int, @ B int, @ c int output
As
Set @ c = @ a + @ B

Go
Declare @ temp int
Exec test 12,23, @ temp output
Select @ temp