A detailed description of the stored procedure-sqlserver

Source: http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html

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

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

2. Number: is an optional integer that is used to group procedures with the same name so that a drop PROCEDURE statement can be used to remove the same set of procedures. For example, an application named orders uses a procedure that can be named Orderproc;1, Orderproc;2, and so on. The drop PROCEDURE orderproc statement will drop the entire group. If the name contains a bounding identifier, the number should not be included in the identifier, only the appropriate delimiter should be used before and after procedure_name.  

[email protected]: 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.  
uses the @ symbol as the first character to specify the parameter name. 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
indicates that there is no maximum limit for output parameters that can be the cursor data type.  

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

6.default: The default value for the parameter. 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, I use a stored procedure to do something about it:

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

-------------create a stored procedure named Getuseraccount----------------Create Procedure Getuseraccountasselect * from Useraccountgo-------------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 Inuseraccountasinsert 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 Inuseraccountreasinsert into UserAccount ( Username,[password],registertime,registerip) VALUES (10,10, ' 2013-01-02 ', ten) return @@ Rowcountgo-------------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, @UserID int Outputasif (@UserName >5) Select @UserID =count (*) from useraccount where userid>25elseset @UserID = 1000go-------------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 procedure with return value, input parameter, output parameter

-------------create a stored procedure named Getuseraccount----------------Create Procedure getuseraccountre1@username nchar, @UserID int Outputasif (@UserName >5) Select @UserID =count (*) from UserAccount where Userid>25elseset @UserID =1000return @@ Rowcountgo-------------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, @UserID int Outputasif (@UserName >5) Select @UserID =count (*) from UserAccount where Userid>25elseset @UserID =1000select * From Useraccountreturn @ @rowcountgo-------------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 Getuseraccountre3asselect * from Useraccountselect * from UserAccount where Userid>5go-------------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.

Summary: We've created a variety of stored procedures above, and see how we call these stored procedures in C #.

C # Calling stored procedures

The stored procedure called here is the various kinds of stored procedures I wrote above.

Public partial class ProcedureTest:System.Web.UI.Page {public static string conn = Configurationmanager.conn ectionstrings["Sturelationdbconnectionstring"].        ConnectionString;        Public SqlConnection con = new SqlConnection (conn);        protected void Page_Load (object sender, EventArgs e) {runGetUserAccountRe3 (); }//Return only stored procedures for a single recordset Getuseraccount public void Rungetuseraccount () {SqlDataAdapter dp = new            SqlDataAdapter (Common ("Getuseraccount"));            DataSet ds = new DataSet (); Populate the dataset DP.            Fill (DS); Rpt.            DataSource = ds; Rpt.        DataBind (); }//The stored procedure without input output inuseraccount public void Runinuseraccount () {con.            Open (); Label1.Text = Common ("Inuseraccount"). ExecuteNonQuery ().            ToString (); Con.        Close (); }//Stored procedure with return value inuseraccountre public void Runinuseraccountre () {//create parameter SqlCommand cmd = Common ("Inuseraccountre");            idataparameter[] Parameters = {New SqlParameter ("Rval", sqldbtype.int,4)}; Set the parameter type to the return value type Parameters[0].            Direction = ParameterDirection.ReturnValue; Add the parameter cmd.            Parameters.Add (Parameters[0]); Con.            Open (); Executes the stored procedure and returns the number of rows affected Label1.Text = cmd. ExecuteNonQuery ().            ToString (); Con.            Close (); Displays the number of rows affected and the return value Label1.Text + = "-" + parameters[0].        Value.tostring (); }//stored procedure with input parameters and output parameters public void Rungetuseraccountre () {SqlCommand cmd = common ("Getuserac            Countre ");                  Create parameter idataparameter[] Parameters = {New SqlParameter ("@UserName", sqldbtype.nchar,20),            New SqlParameter ("@UserID", SqlDbType.Int),}; Set parameter type Parameters[0].              Value = "7"; ParamETERS[1].  Direction = ParameterDirection.Output; Set as output parameter//Add parameter cmd.            Parameters.Add (Parameters[0]); Cmd.            Parameters.Add (Parameters[1]); Con.            Open (); Executes the stored procedure and returns the number of rows affected Label1.Text = cmd. ExecuteNonQuery ().            ToString (); Con.                       Close (); Displays the number of rows affected and the output parameters Label1.Text + = "-" + parameters[1].                   Value.tostring (); }//Stored procedure with return value, input parameter, output parameter GetUserAccountRe1 public void RunGetUserAccountRe1 () {Sqlcomman            D-cmd = Common ("GetUserAccountRe1");                  Create parameter idataparameter[] Parameters = {New SqlParameter ("@UserName", sqldbtype.nchar,20),              New SqlParameter ("@UserID", SqlDbType.Int), New SqlParameter ("Rval", sqldbtype.int,4)            }; Set parameter type Parameters[0].            Value = "7"; PARAMETERS[1].  Direction = ParameterDirection.Output;        Set as output parameter     PARAMETERS[2].  Direction = ParameterDirection.ReturnValue; Set to return value//Add parameter cmd.            Parameters.Add (Parameters[0]); Cmd.            Parameters.Add (Parameters[1]); Cmd.            Parameters.Add (parameters[2]); Con.            Open (); Executes the stored procedure and returns the number of rows affected Label1.Text = cmd. ExecuteNonQuery ().            ToString (); Con.            Close (); Displays the number of rows affected and the output parameter Label1.Text + = "-The output parameter is:" + parameters[1].            Value.tostring (); Label1.Text + = "-The return value is:" + parameters[2].        Value.tostring ();  }//Return both parameters and recordset stored procedures GetUserAccountRe2 public void RunGetUserAccountRe2 () {SqlCommand cmd =            Common ("GetUserAccountRe2");                  Create 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 to output parameter parameters[2].  Direction = ParameterDirection.ReturnValue; Set to return value//Add parameter cmd.            Parameters.Add (Parameters[0]); Cmd.            Parameters.Add (Parameters[1]); Cmd.            Parameters.Add (parameters[2]); Con.            Open (); Executes the stored procedure and returns the number of rows affected Label1.Text = cmd. ExecuteNonQuery ().            ToString ();            DataSet ds = new DataSet ();            SqlDataAdapter dt = new SqlDataAdapter (cmd); Dt.            Fill (DS); Rpt.            DataSource = ds; Rpt.            DataBind (); Con.            Close (); Displays the number of rows affected and the output parameter Label1.Text + = "-The output parameter is:" + parameters[1].            Value.tostring (); Label1.Text + = "-The return value is:" + parameters[2].        Value.tostring ();            }//The stored procedure that returns multiple recordsets 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 up SQL connection cmd.                        Connection = con;            If the execution statement cmd.commandtext = Proname;            Specifies the execution statement for the stored procedure cmd.commandtype = CommandType.StoredProcedure;        return cmd; }    }

Some global variables that are included with SQL Server databases

Select App_name () as W--Application for current session SELECT @ @IDENTITY--Returns the last inserted identity value Select USER_NAME ()--Returns the user database user name SELECT @ @CONNECTION S--Returns the number of connections or attempts to connect since the last SQL startup.  Select GETDATE ()--Current time SELECT @ @CPU_BUSY/100--Returns the working time of the CPU since the last SQL startup, in milliseconds use tempdb SELECT @ @DBTS as W--Returns the current The value of the timestamp data type. This timestamp value is guaranteed to be unique in the database. SELECT @ @IDENTITY as W--Returns the last inserted identity value SELECT @ @IDLE as W--Returns the time that SQL has been idle since the last startup, in milliseconds SELECT @ @IO_BUSY as W--returns SQL from the last boot The time of the input and output operations, in milliseconds SELECT @ @LANGID as W-Returns the local language identifier (ID) of the language currently in use. SELECT @ @LANGUAGE as W--returns the currently used language name SELECT @ @LOCK_TIMEOUT as W-the current lock timeout setting for the current session, in milliseconds. SELECT @ @MAX_CONNECTIONS As W-Returns the maximum number of simultaneous user connections allowed on SQL. The number returned does not have to be the value of the currently configured EXEC sp_configure--Displays the global configuration settings for the current server SELECT @ @MAX_PRECISION as W-returns the precision level used for the decimal and numeric data types, that is, the current Sets the precision. The default maximum precision is 38. SELECT @ @OPTIONS as W-returns information for the current SET option. SELECT @ @PACK_RECEIVED As W-Returns the number of input packets read from the network since SQL started. SELECT @ @PACK_SENT As W-Returns the number of output packets that have been written to the network since the last startup. SELECT @ @PACKET_ERRORS As W-Returns the number of network packet errors that occurred on the SQL connection since SQL startup. SELECT @ @SERVERNAME as W--Returns the name of the running SQL Server.  SELECT @ @SERVICENAME as W--Returns the registry key name under which SQL is running SELECT @ @TIMETICKS as W--the number of microseconds to return a SQL Server moment select @ @TOTAL_ERRORS as W--return The number of disk read/write errors encountered by the SQL Server since it was started. SELECT @ @TOTAL_READ As W-returns the number of times the SQL Server has read the disk since it started. SELECT @ @TOTAL_WRITE As W-returns the number of times the SQL Server has written to disk since startup. 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.

Detailed description of the stored procedure-sqlserver