SQL database stored Procedures sample parsing _mssql

What is stored procedures: stored procedures can be said to be a recordset, it is a number of T-SQL statements composed of blocks of code, these T-SQL statement code like a method to implement a number of functions (for a single table or multiple table additions and deletions to check), and then give the code block name, Call him when you're using this feature.

Benefits of Stored procedures:

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

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

3. The stored procedures enable users without permission to access the database indirectly under control, thereby ensuring data security.

Summary: In short, stored procedures are good things, in doing the project is an essential tool, the following describes the basic syntax of the stored procedure.

Explanation of the syntax and parameters of stored procedures

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 procedure_name '--stored procedure, if there are parameters, followed by the parameter format: @ parameter name =value, can also be directly a parameter value

-------------- Delete a stored procedure-----------------

drop procedure procedure_name  -You can call another stored procedure in a stored procedure, not another stored procedure

To create a parameter for a stored procedure:
1.procedure_name: The name of the stored procedure in front Plus # is a 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 group 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 drops the entire group. If the name contains a bounding identifier, the number should not be included in the identifier, and the appropriate delimiter should be used only before and after procedure_name.

3. @parameter: Parameters for Stored procedures. 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 up to 2.1 parameters.
Use 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, parameters can only be substituted for constants and cannot be used in place of a table name, column name, or other database object name. 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 you specify a data type of cursor, you must also specify both varying and OUTPUT keywords. For more information about the data types and their syntax provided by SQL Server, see Data types.
Indicates that there is no maximum number of restrictions for output parameters that can be cursor data types.

5.VARYING: Specifies the result set that is supported as output parameters (dynamically constructed by stored procedures and content can be changed). Only cursor parameters are 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 execute the procedure. The default value must be constant or NULL. If the procedure uses the LIKE keyword for this parameter, the default value can contain wildcard characters (%, _, [], 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. Output parameters that use the input keyword can be cursor placeholders.

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

9.ENCRYPTION: An entry that contains the text of the CREATE PROCEDURE statement in the SQL Server encryption syscomments table. Use encryption to prevent the process from being published as part of SQL Server replication. Description during the upgrade process, SQL Server uses the encrypted annotations stored in syscomments to recreate the encryption process.

10.FOR REPLICATION: Specifies that stored procedures created for replication cannot be executed at the Subscriber. Stored procedures created using the For REPLICATION option can be used as stored procedure filtering and can only be performed 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: Any number and type of Transact-SQL statements to include in the procedure. But there are some restrictions.

Summary: After reading the basic syntax, I create a variety of stored procedures based on the syntax.

Creating a Stored Procedure

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

1. A stored procedure that returns only a single recordset

-------------creates a stored procedure named Getuseraccount----------------Create
Procedure getuseraccount as
select * FROM UserAccount
Go

-------------Execute the above stored procedure----------------
exec Getuseraccount

Results: This is equivalent to running the SELECT * from UserAccount code, resulting in the entire table's data.

2. Stored procedures without input and output

-------------creates 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: This line of code is equivalent to running INSERT INTO UserAccount (Username,[password],registertime,registerip) VALUES (9,9, ' 2013-01-02 ', 9).

3. Stored procedures with return values

-------------creates a stored procedure named Getuseraccount----------------Create

Procedure inuseraccountre as
insert Into UserAccount (Username,[password],registertime,registerip) VALUES (10,10, ' 2013-01-02 ', ten)
return @@ RowCount
go

-------------Execute the above stored procedure----------------

exec inuseraccountre

Explanation: Here the @ @rowcount the number of rows affected by the execution of the stored procedure, the result is not only inserting a single data, but also returning a value of return value = 1, which can be obtained in the program and later in C # calling stored procedures.

4. Stored procedures with input parameters and output parameters

-------------to 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> /
Else
set @UserID =1000
go

-------------Execute the above stored procedure----------------

exec getuseraccountre ' 7 ', NULL

Explanation: @UserName is an input parameter, @UserID is an output parameter. The run result is @userid to coout (*) = 1.

5. Stored procedures with return values, input parameters, and output parameters

-------------to create a stored procedure named Getuseraccount----------------Create

Procedure GetUserAccountRe1
@UserName nchar (
@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. Return parameters and Recordset stored procedures at the same time

-------------to create a stored procedure named Getuseraccount----------------Create

Procedure GetUserAccountRe2
@UserName nchar (
@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

------------ -Executes the above stored procedure----------------

exec GetUserAccountRe2 ' 7 ', null

Results: Returns the result set of the code that executes the select * from UserAccount, while @userid is coout (*) that is =1,retun value=9.

7. Return stored procedures for multiple recordsets

-------------to create a stored procedure named Getuseraccount----------------Create

Procedure GetUserAccountRe3
as
Select * FROM UserAccount
select * 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.

Summary: We've created all kinds of stored procedures, and here's how we call these stored procedures in C #.

C # calls stored procedures

The stored procedures that are invoked here are the kinds of stored procedures I write above.

Public partial class ProcedureTest:System.Web.UI.Page {public static string conn = Configurationmanager.connecti onstrings["Sturelationdbconnectionstring"].
    ConnectionString;
    Public SqlConnection con = new SqlConnection (conn);
    protected void Page_Load (object sender, EventArgs e) {runGetUserAccountRe3 (); ///returns only a single recordset's stored procedure getuseraccount public void Rungetuseraccount () {SqlDataAdapter dp = new Sqldataadapte
      R (Common ("Getuseraccount"));
      DataSet ds = new DataSet (); Populate the dataset DP.
      Fill (DS); Rpt.
      DataSource = ds; Rpt.

    DataBind (); ///No stored procedure with input output inuseraccount public void Runinuseraccount () {con.
      Open (); Label1.Text = Common ("Inuseraccount"). ExecuteNonQuery ().
      ToString (); Con.
    Close (); ///There is a stored procedure with return value inuseraccountre public void Runinuseraccountre () {///create parameter SqlCommand cmd = Common (
      "Inuseraccountre"); 
         idataparameter[] Parameters = {New SqlParameter ("Rval", sqldbtype.int,4)}; Sets the parameter type to the return value type Parameters[0].
      Direction = ParameterDirection.ReturnValue; Add 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 (); Shows the number of rows affected and the return value Label1.Text + = "-" + parameters[0].
    Value.tostring ();
      The stored procedure with input and output parameters is public void Rungetuseraccountre () {SqlCommand cmd = common ("Getuseraccountre"); 
         Create 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 to output parameters//Add parameters 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 (); Shows the number of rows affected and output parameters Label1.Text + = "-" + parameters[1].
      
    Value.tostring (); ///The stored procedure with return value, input parameter, output parameter GetUserAccountRe1 public void RunGetUserAccountRe1 () {SqlCommand cmd = Commo
      N ("GetUserAccountRe1");  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 add parameter cmd for the return value//.
      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 (); Shows the number of rows affected and output parameters Label1.Text+ = "-The output parameter is:" + parameters[1].
      Value.tostring (); Label1.Text + = "-The return value is:" + parameters[2].

    Value.tostring (); ///returns both parameters and recordset stored procedures GetUserAccountRe2 public void RunGetUserAccountRe2 () {SqlCommand cmd = common ("Getu
      SerAccountRe2 ");  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 add parameter cmd for the return value//.
      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 (); Shows the number of rows affected and output parameters Label1.Text + = "-The output parameter is:" + parameters[1].
      Value.tostring (); Label1.Text + = "-The return value is:" + parameters[2].

    Value.tostring ();
      //Return stored procedures for 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 that the execution statement is a stored procedure cmd.commandtype = CommandType.StoredProcedure;
    return cmd;

 }
  }

Some global variables shipped with the SQL Server database

Select App_name () as W--application of the current session SELECT @ @IDENTITY--Returns the last inserted identity value Select USER_NAME ()--Returns the user database username SELECT @ @CONNECT 
Ions-Returns the number of connections or attempts to connect since the last SQL startup.  Select GETDATE ()--Current time SELECT @ @CPU_BUSY/100-Returns the CPU's working time since the last time the SQL was started, 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 the SQL that was used to execute since the last startup 
Time of 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 current configured numeric EXEC sp_configure-Displays the global configuration settings for the current server SELECT @ @MAX_PRECISION as W-returns the level of precision used for the decimal and numeric data types, that is, when the The precision before setting. 
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 packets that SQL has read from the network since it was started. 
SELECT @ @PACK_SENT As W-Returns the number of output packets that have been written to the network since the last boot. 
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 that SQL is running under SELECT @ @TIMETICKS as W-Returns the number of microseconds for a quarter of SQL Server 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 reads disks since it was started. 
SELECT @ @TOTAL_WRITE As W-returns the number of times the SQL Server has written to the disk since it was 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 the entire content of this article, I hope to help you learn.