Detailed SQL stored procedures

Introduction to Stored Procedures

What is a stored procedure : A stored procedure can be said to be a recordset, which is a block of code consisting of some T-SQL statements that implement functions like a method (to and from a single table or multiple tables), and then give the code block a name, Call him when you use this feature.

benefits of stored procedures :

1. Because the database executes the action, it is compiled and executed first. However, a stored procedure is a compiled block of code, so execution is more efficient than T-SQL statements.

2. A stored procedure can replace a large number of T-SQL statements when the program interacts with the network, so it can reduce the traffic of the network and improve the communication rate.

3. The stored procedures enable users who do not have permissions to access the database indirectly under control, thereby ensuring the security of the data.

Summary: In short, the stored procedure is a good thing, in the project is a necessary tool, the following describes the basic syntax of the stored procedure.

Explanation of the syntax and parameters of the stored procedure

Some basic syntax for stored procedures:

--------------creating a Stored procedure-----------------create PROC [edure] procedure_name [; number] [{@parameter data_type} [VARYING] [= default] [OUTPUT]] [,... N] [With {RECOMPILE | Encryption | RECOMPILE, encryption} [for REPLICATION]as sql_statement [... n]--------------call the stored procedure-----------------EXECUTE procedu Re_name '--stored procedure if there are parameters, the following parameter format is: @ parameter name =value, can also be directly for the parameter value of the--------------Delete stored procedure-----------------drop procedure Procedure_name--can call another stored procedure in the stored procedure, but cannot delete another stored procedure

to create parameters for a stored procedure:
1.procedure_name: The name of the stored procedure, preceded by # for the local temporary stored procedure, plus # #为全局临时存储过程.

2.
3. @parameter: The parameters of the stored procedure. can have one or more. The user must provide the value of each declared parameter when the procedure is executed (unless the default value for that parameter is defined). A stored procedure can have a maximum of 2.1 parameters. The
uses the @ symbol as the first character to specify the name of the parameter. Parameter names must conform to the rules for identifiers. The parameters for each procedure are used only for the procedure itself, and the same parameter names can be used in other procedures. By default, a parameter can be used instead of a constant instead of the name of a table name, column name, or other database object. For more information, see EXECUTE.

4.data_type: The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures. However, the cursor data type can only be used for OUTPUT parameters. If the specified data type is cursor, you must also specify both the VARYING and the OUTPUT keywords. For more information about the data types provided by SQL Server and their syntax, see Data types. The
description does not have a maximum number of limits for output parameters that can be the cursor data type.

5.varying: Specifies the result set that is supported as an output parameter (dynamically constructed by the stored procedure and content can vary). Only the cursor parameter is applicable.  

6.Default: defaults for parameters. If you define a default value, you do not have to specify the value of the parameter to perform the procedure. The default value must be constant or NULL. If the procedure uses the LIKE keyword for the parameter, the default value can include wildcards (%, _, [], and [^]).

7.OUTPUT: Indicates that the parameter is a return parameter. The value of this option can be returned to Exec[ute]. Use the OUTPUT parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. The output parameter that is used with the Export keyword can be a cursor placeholder.

8.RECOMPILE: Indicates that SQL Server does not cache the schedule for this procedure, which is recompiled at run time. Use the RECOMPILE option when you are using atypical or temporary values and you do not want to overwrite the execution plan that is cached in memory.

9.ENCRYPTION: Represents an entry in SQL Server encrypted syscomments table that contains the text of the CREATE PROCEDURE statement. Use encryption to prevent the process from being published as part of SQL Server replication. Description during the upgrade process, SQL Server re-creates the encryption process by leveraging the cryptographic annotations stored in syscomments.

10.FOR REPLICATION: Specifies that stored procedures created for replication cannot be performed at the Subscriber. The stored procedure that is created with the For REPLICATION option can be used as a stored procedure filter and can only be executed during the replication process. This option cannot be used with the WITH RECOMPILE option.

11.AS: Specifies the action to be performed by the procedure.

12.sql_statement: A Transact-SQL statement of any number and type to be included in the procedure. But there are some limitations.

Summary: After reading these basic grammars, I'll create a variety of stored procedures based on the syntax below.

Create a stored procedure

UserAccount
Userid	UserName	PassWord	Registertime	Registerip
12	6	6	2012-12-31	6
18	5	5	2013-01-01	5
19	1	1	2013-01-01	1
20	2	2	2013-01-01	2
21st	3	3	2013-01-01	3
22	4	4	2013-01-01	4
23	5	5	2013-01-01	5
25	7	7	2013-01-01	7
26	8	8	2013-01-01	8
Null	Null	Null	Null	Null

For the table above, use a stored procedure to do something about it:

CREATE TABLE structure
CREATE TABLE [dbo]. [UserAccount] (
[UserID] [INT] IDENTITY (*) is not NULL,
[UserName] [varchar] (Ten) NULL,
[PassWord] [varchar] (Ten) NULL,
[Registertime] [varchar] () NULL,
[Registerip] [INT] Null
) on [PRIMARY]


TRUNCATE TABLE UserAccount; Emptying the UserAccount table

Inserting data
INSERT into UserAccount (username,password,registertime,registerip) VALUES (' 6 ', ' 6 ', ' 2012-12-31 ', 6);
INSERT into UserAccount (username,password,registertime,registerip) VALUES (' 5 ', ' 5 ', ' 2013-01-01 ', 5);
INSERT into UserAccount (username,password,registertime,registerip) VALUES (' 1 ', ' 1 ', ' 2013-01-01 ', 1);
INSERT into UserAccount (username,password,registertime,registerip) VALUES (' 2 ', ' 2 ', ' 2013-01-01 ', 2);
INSERT into UserAccount (username,password,registertime,registerip) VALUES (' 3 ', ' 3 ', ' 2013-01-01 ', 3);
INSERT into UserAccount (username,password,registertime,registerip) VALUES (' 4 ', ' 4 ', ' 2013-01-01 ', 4);
INSERT into UserAccount (username,password,registertime,registerip) VALUES (' 5 ', ' 5 ', ' 2013-01-01 ', 5);
INSERT into UserAccount (username,password,registertime,registerip) VALUES (' 7 ', ' 7 ', ' 2013-01-01 ', 7);
INSERT into UserAccount (username,password,registertime,registerip) VALUES (' 8 ', ' 8 ', ' 2013-01-01 ', 8);

View data in a table
SELECT * from UserAccount;

//////////////////////////////////////////////////////////////////////////////
Stored Procedure Case////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////

1. A stored procedure that returns only a single record set

-------------create a stored procedure named Getuseraccount----------------
Create Procedure Getuseraccount
As
SELECT * FROM UserAccount
Go

-------------execute the above stored procedure----------------
EXEC Getuseraccount
Result: the equivalent of running the SELECT * from UserAccount Line of code, resulting in data for the entire table.


2. Stored procedures without input and output

-------------create a stored procedure named Getuseraccount----------------
Create Procedure Inuseraccount
As
Insert into UserAccount (Username,[password],registertime,registerip) VALUES (9,9, ' 2013-01-02 ', 9)
Go

-------------execute the above stored procedure----------------
EXEC Inuseraccount
Result: equivalent to running insert INTO UserAccount (Username,[password],registertime,registerip) VALUES (9,9, ' 2013-01-02 ', 9) This line of code.


3. Stored procedure with return value
-------------create a stored procedure named Getuseraccount----------------

Create Procedure Inuseraccountre
As
Insert into UserAccount (Username,[password],registertime,registerip) VALUES (10,10, ' 2013-01-02 ', 10)
return @ @rowcount
Go

-------------execute the above stored procedure----------------

EXEC inuseraccountre
Explanation: The @ @rowcount here is the number of rows affected by the execution of the stored procedure, and the result is not only inserting a piece of data, but also returning a value of return value = 1, which can be obtained in the program and later in the C # call to the stored procedure.


4. Stored procedures with input parameters and output parameters
-------------create a stored procedure named Getuseraccount----------------

Create Procedure Getuseraccountre
@UserName nchar (20),
@UserID int Output
As
if (@UserName >5)
Select @UserID =count (*) from UserAccount where userid>25
Else
Set @UserID =1000
Go

-------------execute the above stored procedure----------------

EXEC getuseraccountre ' 7 ', NULL
Explanation: @UserName as an input parameter, @UserID as an output parameter. The result is @userid to coout (*) = 1.


5. Stored procedures with return values, input parameters, and output parameters
-------------create a stored procedure named Getuseraccount----------------

Create Procedure GetUserAccountRe1
@UserName nchar (20),
@UserID int Output
As
if (@UserName >5)
Select @UserID =count (*) from UserAccount where userid>25
Else
Set @UserID =1000
return @ @rowcount
Go

-------------execute the above stored procedure----------------

EXEC GetUserAccountRe1 ' 7 ', NULL
Results: The @userID was Coout (*), i.e. =1,retun value=1.


6. Simultaneous return of parameters and record set stored procedures
-------------create a stored procedure named Getuseraccount----------------

Create Procedure GetUserAccountRe2
@UserName nchar (20),
@UserID int Output
As
if (@UserName >5)
Select @UserID =count (*) from UserAccount where userid>25
Else
Set @UserID =1000
SELECT * FROM UserAccount
return @ @rowcount
Go

-------------execute the above stored procedure----------------

EXEC GetUserAccountRe2 ' 7 ', NULL
Result: Returns the result set of the code that executes the select * from UserAccount, while @userid is coout (*), which is =1,retun value=9.


7. A stored procedure that returns multiple recordsets
-------------create a stored procedure named Getuseraccount----------------

Create Procedure GetUserAccountRe3
As
SELECT * FROM UserAccount
SELECT * FROM UserAccount where userid>5
Go

-------------execute the above stored procedure----------------

EXEC GetUserAccountRe3
Results: Returns two result sets, one for select * from UserAccount and the other for select * from UserAccount where userid>5.

Detailed SQL stored procedures