SQL database Stored Procedure example parsing and SQL stored procedure example

SQL database Stored Procedure example parsing and SQL stored procedure example

What is a stored procedure:Stored procedures can be said to be a record set, it is a code block composed of some T-SQL statements, these T-SQL statement code implements some functionality like a method (add, delete, modify, and query a single table or multiple tables), and then give the code block a name, you can call this function when using this function.

Advantages of stored procedures:

1. Because the database first compiles and then executes the action. However, the stored procedure is a compiled code block, so the execution efficiency is higher than the T-SQL statement.

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

3. stored procedures allow unauthorized users to indirectly access the database under control to ensure data security.

Summary: stored procedures are good things. They are essential tools for projects. The following describes the basic syntax of stored procedures.

Explanation of Stored Procedure syntax and Parameters

Some basic syntax of the stored procedure:

-------------- CREATE 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 Stored Procedure ----------------- EXECUTE Procedure_name ''-- if the stored procedure has parameters, the following parameter format is added: @ parameter name = value, you can also delete the Stored procedure ------------------------- drop procedure procedure_name directly as the parameter value. You can call another stored procedure in the stored procedure, but cannot delete another stored procedure.

Parameters for creating a stored procedure:
1. procedure_name: name of the stored procedure. Add # as a local temporary stored procedure, and add # As a global temporary stored procedure.

2.; number: an optional integer used to group processes with the same name, so that the same group can be removed with a drop procedure statement. For example, the process used by an application named orders can be named orderproc; 1, orderproc; 2, and so on. The drop procedure orderproc statement removes the entire group. If the name contains a bound identifier, the number should not be included in the identifier and the appropriate delimiters should be used before and after procedure_name.

3. @ parameter: the parameter of the stored procedure. There can be one or more. You must provide the value of each declared parameter during execution (unless the default value of this parameter is defined ). A stored procedure can have a maximum of 2.100 parameters.
Use the @ symbol as the first character to specify the parameter name. The parameter name must comply with the identifier rules. Each process parameter is only used for this process. The same parameter name can be used in other processes. By default, parameters can only replace constants, but cannot replace the names of table names, column names, or other database objects. For more information, see EXECUTE.

4. data_type: 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, the VARYING and OUTPUT keywords must also be specified. For more information about the data types and syntax provided by SQL Server, see data types.
It indicates that there is no limit on the maximum number of output parameters that can be cursor data types.

5. VARYING: Specify the result set supported by the output parameter (the result set is dynamically constructed by the stored procedure and the content can be changed ). Only applicable to cursor parameters.

6. 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 LIKE keyword is used for this parameter, the default value can contain wildcards (%, _, [], and [^]).

7. OUTPUT: indicates that the parameter is a return parameter. The value of this option can be returned to EXEC [UTE]. The OUTPUT parameter can be used to return information to the call process. The Text, ntext, and image parameters can be used as OUTPUT parameters. The OUTPUT parameter using the OUTPUT keyword can be a placeholder cursor.

8. RECOMPILE: indicates that SQL Server does not cache the plan of the process, and the process will be re-compiled at runtime. Use the RECOMPILE option when you use an atypical or temporary value instead of overwriting the execution plan cached in the memory.

9. ENCRYPTION: indicates the entries in the SQL Server encrypted syscomments table that contain the CREATE PROCEDURE statement text. ENCRYPTION prevents the process from being published as part of SQL Server replication. It indicates that during the upgrade, SQL Server uses the encryption annotation stored in syscomments to recreate the encryption process.

10. for replication: specifies that the stored procedure created for replication cannot be executed on the subscription server .. Stored Procedures created using the for replication option can be used FOR filtering stored procedures and can only be executed during REPLICATION. This option cannot be used WITH the with recompile option.

11. AS: Specifies the operation to be performed in the process.

12. SQL _statement: Any number and types of Transact-SQL statements to be included in the process. But there are some restrictions.

Summary: After reading these basic syntaxes, I will create various stored procedures based on these syntaxes.

Create a stored procedure

 

For the above table, I use the stored procedure to perform some operations on it:

1. Only the stored procedure of a single record set is returned.

------------- Create a stored Procedure named GetUserAccount -------------- create Procedure GetUserAccountasselect * from UserAccountgo ------------- execute the preceding Stored Procedure ---------------- exec GetUserAccount

Result: It is equivalent to running the code select * from UserAccount, and the result is the data of the entire table.

2. Stored Procedure without input/output

------------- Create a stored Procedure named GetUserAccount -------------- create Procedure inUserAccountasinsert into UserAccount (UserName, [PassWord], RegisterTime, RegisterIP) values (2013-01-02 ', 9) go ------------- execute the above Stored Procedure -------------- exec inUserAccount

Result: The line of code is equivalent to running insert into UserAccount (UserName, [PassWord], RegisterTime, RegisterIP) values (9, 9, '2017-01-02 ', 9.

3. Stored Procedures with returned values

------------- Create a stored Procedure named GetUserAccount -------------- create Procedure inUserAccountReasinsert into UserAccount (UserName, [PassWord], RegisterTime, RegisterIP) values (2013, '2017-01-02 ', 10) return @ rowcountgo ------------- execute the preceding Stored Procedure -------------- exec inUserAccountRe

Explanation: @ rowcount here is the number of rows affected by the execution of the stored procedure. The execution result is that not only a piece of data is inserted, but also a value is returned, that is, return value = 1, this can be obtained in the program and will be discussed later in the c # Call stored procedure.

4. Stored Procedures with Input and Output Parameters

------------- Create a stored Procedure named GetUserAccount -------------- create Procedure GetUserAccountRe @ UserName nchar (20), @ UserID int outputasif (@ UserName> 5) select @ UserID = COUNT (*) from UserAccount where UserID> 25 elseset @ UserID = 1000go ------------- execute the preceding Stored Procedure -------------- exec GetUserAccountRe '7', null

Explanation: @ UserName is the input parameter and @ UserID is the output parameter. The running result is @ userID = COOUT (*), that is, = 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 outputasif (@ UserName> 5) select @ UserID = COUNT (*) from UserAccount where UserID> 25 elseset @ UserID = 1000 return @ rowcountgo ------------- execute the preceding Stored Procedure -------------- exec GetUserAccountRe1 '7', null

Result: @ userID is COOUT (*) = 1, and Retun Value = 1.

6. Stored Procedures that return both parameters and Record Sets

------------- Create a stored Procedure named GetUserAccount -------------- create Procedure GetUserAccountRe2 @ UserName nchar (20), @ UserID int outputasif (@ UserName> 5) select @ UserID = COUNT (*) from UserAccount where UserID> 25 elseset @ UserID = 1000 select * from UserAccountreturn @ rowcountgo ------------- execute the above Stored Procedure -------------- exec GetUserAccountRe2 '7', null

Result: The result set of the Code select * from UserAccount is returned, and @ userID is COOUT (*), that is, = 1, Retun Value = 9.

7. Return the stored procedure of multiple Record Sets

------------- Create a stored Procedure named GetUserAccount -------------- create Procedure GetUserAccountRe3asselect * from UserAccountselect * from UserAccount where UserID> 5go ------------- execute the Stored Procedure ---------------- GetUserAccountRe3

Result: two result sets, select * from UserAccount and select * from UserAccount where UserID> 5, are returned.

Summary: we have created various stored procedures. The following describes how to call these stored procedures in c.

C # Call a stored procedure

The stored procedures called here are the various stored procedures I wrote above.

Public partial class ProcedureTest: System. web. UI. page {public static string conn = ConfigurationManager. connectionStrings ["StuRelationDBConnectionString"]. connectionString; public SqlConnection con = new SqlConnection (conn); protected void Page_Load (object sender, EventArgs e) {runGetUserAccountRe3 ();} // only GetUserAccount public void runGetUserAccount () {SqlDataAdapter dp = new SqlDataAdapter (common ("GetUserAccount"); DataSet ds = new DataSet (); // fill in dataset dp. fill (ds); rpt. dataSource = ds; rpt. dataBind ();} // Stored Procedure inUserAccount public void runinUserAccount () {con. open (); Label1.Text = common ("inUserAccount "). executeNonQuery (). toString (); con. close () ;}// stored procedure with returned values inUserAccountRe public void runinUserAccountRe () {// create the SqlCommand cmd = common ("inUserAccountRe") parameter "); IDataParameter [] parameters = {new SqlParameter ("rval", SqlDbType. int, 4)}; // set the parameter type to the return value type parameters [0]. direction = ParameterDirection. returnValue; // Add the cmd parameter. parameters. add (parameters [0]); con. open (); // execute the stored procedure and return the affected rows Label1.Text = cmd. executeNonQuery (). toString (); con. close (); // display the affected rows and returned values Label1.Text + = "-" + parameters [0]. value. toString () ;}// stored procedure with input and output parameters public void runGetUserAccountRe () {SqlCommand cmd = common ("GetUserAccountRe "); // create the parameter IDataParameter [] parameters = {new SqlParameter ("@ UserName", SqlDbType. NChar, 20), new SqlParameter ("@ UserID", SqlDbType. int),}; // set the parameter type parameters [0]. value = "7"; parameters [1]. direction = ParameterDirection. output; // set it to the Output parameter // Add the cmd parameter. parameters. add (parameters [0]); cmd. parameters. add (parameters [1]); con. open (); // execute the stored procedure and return the affected rows Label1.Text = cmd. executeNonQuery (). toString (); con. close (); // display the affected number of rows and the output parameter Label1.Text + = "-" + parameters [1]. value. toString () ;}// the Stored Procedure GetUserAccountRe1 public void runGetUserAccountRe1 () {SqlCommand cmd = common ("GetUserAccountRe1") with both return values, input parameters, and output parameters "); // create the parameter IDataParameter [] parameters = {new SqlParameter ("@ UserName", SqlDbType. NChar, 20), new SqlParameter ("@ UserID", SqlDbType. int), new SqlParameter ("rval", SqlDbType. int, 4)}; // set the parameter type parameters [0]. value = "7"; parameters [1]. direction = ParameterDirection. output; // set it to the Output parameter parameters [2]. direction = ParameterDirection. returnValue; // set to return value // Add the cmd parameter. parameters. add (parameters [0]); cmd. parameters. add (parameters [1]); cmd. parameters. add (parameters [2]); con. open (); // execute the stored procedure and return the affected rows Label1.Text = cmd. executeNonQuery (). toString (); con. close (); // display the affected number of rows and the output parameter Label1.Text + = "-output parameter:" + parameters [1]. value. toString (); Label1.Text + = "-return value:" + parameters [2]. value. toString () ;}// the Stored Procedure GetUserAccountRe2 public void runGetUserAccountRe2 () {SqlCommand cmd = common ("GetUserAccountRe2") of both parameters and record set is returned "); // create the parameter IDataParameter [] parameters = {new SqlParameter ("@ UserName", SqlDbType. NChar, 20), new SqlParameter ("@ UserID", SqlDbType. int), new SqlParameter ("rval", SqlDbType. int, 4)}; // set the parameter type parameters [0]. value = "7"; parameters [1]. direction = ParameterDirection. output; // set it to the Output parameter parameters [2]. direction = ParameterDirection. returnValue; // set to return value // Add the cmd parameter. parameters. add (parameters [0]); cmd. parameters. add (parameters [1]); cmd. parameters. add (parameters [2]); con. open (); // execute the stored procedure and return the affected rows Label1.Text = cmd. executeNonQuery (). toString (); DataSet ds = new DataSet (); SqlDataAdapter dt = new SqlDataAdapter (cmd); dt. fill (ds); rpt. dataSource = ds; rpt. dataBind (); con. close (); // display the affected number of rows and the output parameter Label1.Text + = "-output parameter:" + parameters [1]. value. toString (); Label1.Text + = "-return value:" + parameters [2]. value. toString () ;}// return the Stored Procedure public void runGetUserAccountRe3 () {DataSet ds = new DataSet (); sqlDataAdapter dt = new SqlDataAdapter (common ("GetUserAccountRe3"); dt. fill (ds); rpt1.DataSource = ds. tables [0]. defaultView; rpt1.DataBind (); rpt2.DataSource = ds. tables [1]. defaultView; rpt2.DataBind ();} public SqlCommand common (string proName) {SqlCommand cmd = new SqlCommand (); // set the SQL connection cmd. connection = con; // If you execute the command cmd. commandText = proName; // specify the execution statement as the Stored Procedure cmd. commandType = CommandType. storedProcedure; return cmd ;}}

With some global variables of the SQLServer Database

Select APP_NAME () as w -- Application of the current Session select @ IDENTITY -- returns the last inserted id value select USER_NAME () -- return the user database username SELECT @ CONNECTIONS -- returns the number of CONNECTIONS or attempts since the last SQL statement was started. Select getdate () -- current time SELECT @ CPU_BUSY/100 -- returns the CPU time since the last SQL startup, unit: millisecond USE tempdb SELECT @ DBTS as w -- returns the current timestamp data type value for the current database. This timestamp value must be unique in the database. Select @ IDENTITY as w -- returns the last inserted id value SELECT @ IDLE as w -- returns the time when the SQL statement has been IDLE since the last startup, the Unit is millisecond SELECT @ IO_BUSY AS w -- returns the time when the SQL statement was used to perform input and output operations since the last time it was started, unit: millisecond SELECT @ langid as w -- returns the local language identifier (ID) of the current language ). SELECT @ language as w -- return the current LANGUAGE name SELECT @ LOCK_TIMEOUT as w -- set the current lock timeout for the current session, in milliseconds. SELECT @ MAX_CONNECTIONS as w -- returns the maximum number of simultaneous user connections allowed on SQL. The returned number does not need to be the current value EXEC sp_configure -- display the global configuration of the current server to set SELECT @ MAX_PRECISION as w -- return the precision level used by the decimal and numeric data types, that is, the precision currently set on the server. The default maximum precision is 38. Select @ OPTIONS as w -- returns information about the current SET option. SELECT @ PACK_RECEIVED as w -- returns the number of input data packets read from the network after SQL is started. SELECT @ PACK_SENT as w -- return the number of output data packets written to the network by SQ since the last start. SELECT @ PACKET_ERRORS as w -- returns the number of network packet errors that have occurred on the SQL connection since the SQL startup. SELECT @ SERVERNAME as w -- return the name of the running SQL Server. SELECT @ SERVICENAME as w -- return the registry key name that is running under the SQL statement SELECT @ TIMETICKS as w -- return the number of microseconds on the SQL server at a scale SELECT @ TOTAL_ERRORS AS w -- return the SQL after the server is started, number of disk read/write errors. SELECT @ TOTAL_READ as w -- returns the number of times the SQL Server reads the disk after it is started. SELECT @ TOTAL_WRITE as w -- return the number of times the SQL server writes data to the disk after it is started. SELECT @ TRANCOUNT as w -- returns the number of active transactions for the current connection. SELECT @ VERSION as w -- returns the date, VERSION, and processor type of the SQL Server installation.

The above is all the content of this article, hoping to help you learn.

Articles you may be interested in:

• A Stored Procedure for viewing the space usage of the MSSQLServer database, SpaceUsed
• How to copy a table to a database using SQL Server
• SQL checks whether the database, table, stored procedure, and other codes exist.
• Mysql statements used to query stored procedures and functions in a database
• How to delete and restore dangerous stored procedures of SQL Server databases
• MSSQL MySQL database paging (Stored Procedure)
• SQL Server implements remote database backup and recovery through extended stored procedures
• Introduction to mysql Database Import and Export, functions, and stored procedures
• How can I batch authorize stored procedures/functions in a SQL database?
• MSSQL monitors database DDL operations (create, modify, delete stored procedures, create, modify, and delete tables)