Introduction to stored procedure in MS SQL server

I. Concept of stored procedure

Like C, T-SQL is a structured language.

What is a stored procedure?

A stored procedure is a set of pre-compiled SQL query statements and control flow statements, and is saved in a database with a specific name. Stored procedures are also database objects

Category:

System stored procedure: start with sp _ or xp _

Custom: start with proc _

Advantages of stored procedures:

Fast and efficient execution

Modular programming

Reduce network traffic

Improve security

II. System stored procedures

SQL server System stored procedures are stored in the master database, and all named system stored procedures start with "Sp. In the master database,

The number of system stored procedures is as follows:

The code is as follows:	Copy code
Select count ([name]) as 'system storage Qty 'from sysobjects Where [name] like 'sp _ %'

EXECUTE is used to call a stored procedure. It can also be abbreviated as EXEC,

The syntax for calling storage is as follows:

EXECUTE 'stored procedure name' 'parameter' --- If no parameter exists, the parameter is omitted.

Common system stored procedures

EXEC sp_databases: list databases in the current system
EXEC sp_renamedb 'northwind ', 'northwind1' modify the database name (accessed by a single user)
USE stuDB go exec sp_tables returns information about a table column.
EXEC sp_columns
EXEC sp_help
EXEC sp_helpconstraint 'table name' view the constraints of a table
EXEC sp_helpdb 'database name' or EXEC sp_helpdb to view the specified database or all database information
EXEC sp_helptext 'object name' displays the definition text of the database object (stored procedure, trigger, attempt)
EXEC sp_helpindex 'table name' to view the index information of the specified table
EXEC sp_renamedb 'original name', 'new name' change database name
EXEC sp_stored_procedures list all stored procedures available in the current environment

In addition to system stored procedures, SQL Server also provides extended stored procedures starting with Xp _. For example, you can call the xp_mongoshell stored procedure named after DOS.

The usage is as follows:

 

The code is as follows:	Copy code
EXEC Xp_cmdshell DOS name [NO_OUTPUT]

NO_OUTPUT is an optional parameter, indicating whether to input the information returned by the stored procedure

3. User-defined stored procedures

1. Syntax

The code is as follows:	Copy code
Create procedure stored procedure name @ Parameter 1 data type [= default value] [parameter type (input/output)] ...... @ Parameter n data type [= default value] [parameter type (input/output)] As Begin SQL statement End; Go

 
Parameter types include input parameters and OUTPUT parameters. The default value is input parameters. OUTPUT is used to represent OUTPUT parameters. It is best to create a stored procedure starting with proc
 

2. Create a stored procedure without parameters
 

The code is as follows:	Copy code
-- Determine whether a stored procedure exists If object_id ('proc _ student ', 'Procedure') is not null Drop procedure proc_student Go Create procedure proc_student As Begin Select pcid as 'computer number ', Case pcuse When 0 then 'id' When 1 then 'busy' End as 'usage status' from pc End; -- Call a stored procedure Execute proc_student select * from pc Go

3. Create a stored procedure with input parameters

Syntax:

The code is as follows:	Copy code
Create procedure stored procedure name @ Parameter 1 data type [= default value] .... @ Parameter 2 data type [= default value] As SQL and statements ... Go

 

-- For example

-- Create a stored procedure with input parameters

The code is as follows:	Copy code
If object_id ('proc _ Stu', 'Procedure ') is not null Drop procedure proc_stu Go Create procedure proc_stu @ Pcuse int As Begin Select pcid as 'computer number ', Case pcuse When 0 then 'id' When 1 then 'busy' End as 'usage status' from pc where pcuse = @ pcuse end; -- Call a stored procedure Execute proc_stu @ pcuse = 1

4. Create a stored procedure with output parameters

The code is as follows:	Copy code
-- Create a stored procedure with output parameters If OBJECT_ID ('proc _ s', 'Procedure ') is not null Drop procedure proc_s Go Create procedure proc_s @ Pcid int, @ Pcus int output As Begin Select @ pcus = pcuse from pc where pcid = @ pcid end; -- Call a stored procedure Declare @ pcus int execute proc_s 5, @ pcus output

4. Handle error messages

When statements in a stored procedure are complex, you can add an error language to the stored procedure. In SQL Server, you can use RAISERROR to return custom error messages.

The RAISERROR syntax is as follows:

 
RAISERROR (custom error message, error severity, error status)

 
Custom error message: indicates output information: indicates output error prompt text

Severity level of the error: indicates the severity level of the custom error. (0-18 pole)

Error status: indicates the custom error status. The value range is 1-127.

Example

Write your own paging storage process. After finishing the process, give a complete example.
First, create a paging stored procedure. The following paging stored procedure is modified based on the paging stored procedure provided by pbsql.

The code is as follows:

Copy code

Create procedure sp_page @ StrTable varchar (50), -- table name @ StrColumn varchar (50), -- pagination by this column @ IntColType int, -- @ strColumn column type, 0-number type, 1-character type, 2-date and time type @ IntOrder bit, -- sort, 0-order, 1-inverted @ StrColumnlist varchar (800), -- List of fields to be queried, * indicates all fields @ IntPageSize int, -- number of records per page @ IntPageNum int, -- specifies the page @ StrWhere varchar (800), -- query condition @ IntPageCount int OUTPUT -- Total number of pages AS DECLARE @ SQL nvarchar (4000) -- used to construct SQL statements DECLARE @ where1 varchar (800) -- construct a condition statement DECLARE @ where2 varchar (800) -- construct a condition statement IF @ strWhere is null or rtrim (@ strWhere) ='' -- To prevent the SQL keywords from being connected to fields and table names, add spaces for the input variables. BEGIN -- no query conditions SET @ where1 = 'where' SET @ where2 ='' END ELSE BEGIN -- with query conditions SET @ where1 = 'Where ('+ @ strWhere +') AND' SET @ where2 = 'Where ('+ @ strWhere + ')' END Set @ strColumn = ''+ @ strColumn +'' Set @ strColumnlist = ''+ @ strColumnlist +'' -- Construct an SQL statement to calculate the total number of pages. Formula for calculating total pages = Ceiling (number of records/page size) http://www.111cn.net SET @ SQL = 'SELECT @ intPageCount = CEILING (COUNT (*) + 0.0 )/' + CAST (@ intPageSize AS varchar) + ') FROM' + @ strTable + @ where2 -- Execute the SQL statement, calculate the total number of pages, and put it into the @ intPageCount variable EXEC sp_executesql @ SQL, n' @ intPageCount int output', @ intPageCount OUTPUT -- Place the total number of pages before the first field in the returned record set. This statement can be omitted. SET @ strColumnlist = Cast (@ intPageCount as varchar (30) + 'As PageCount, '+ @ strColumnlist IF @ intOrder = 0 -- construct an SQL statement in ascending order SET @ SQL = 'SELECT TOP '+ CAST (@ intPageSize AS varchar) + @ StrColumnlist + 'From' + @ strTable + @ where1 + @ StrColumn + '> (select max (' + @ strColumn + ')' + 'From (select top '+ CAST (@ intPageSize * (@ intPageNum-1) AS varchar) + @ StrColumn + 'from' + @ strTable + @ where2 + 'Order by' + @ strColumn + ') t) ORDER by' + @ strColumn ELSE -- construct a descending SQL statement SET @ SQL = 'SELECT TOP '+ CAST (@ intPageSize AS varchar) + @ StrColumnlist + 'From' + @ strTable + @ where1 + @ StrColumn + '<(select min (' + @ strColumn + ')' + 'From (select top '+ CAST (@ intPageSize * (@ intPageNum-1) AS varchar) + @ StrColumn + 'from' + @ strTable + @ where2 + 'Order by' + @ strColumn + 'desc) t) ORDER by' + @ StrColumn + 'desc' IF @ intPageNum = 1 -- page 1 SET @ SQL = 'SELECT top' + CAST (@ intPageSize AS varchar) + @ strColumnlist + 'from' + @ strTable + @ Where2 + 'Order by' + @ strColumn + CASE @ intOrder WHEN 0 then' 'else' DESC' END -- PRINT @ SQL EXEC (@ SQL) GO

Next, create a data table to test and add 123 test data to the table.

The code is as follows:

Copy code

If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [myUser] ') and OBJECTPROPERTY (id, n'isusertable') = 1) Drop table [dbo]. [myUser] GO Create table [dbo]. [myUser] ( [UserId] [int] IDENTITY (1, 1) not null, [UserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO Declare @ I int Set @ I = 1 While (@ I <= 123) Begin Insert into myUser (UserName) values ('test' + cast (@ I as varchar (6 ))) Set @ I = @ I + 1 End Call stored procedure Declare @ o int Exec sp_page 'myuser', 'userid', 0, 0, '*', 15, 1, '', @ o output Exec sp_page 'myuser', 'userid', 0, 0, '*', 15, 9, '', @ o output If it is in. Net, you can use output parameters. The following example shows the output parameters of C # from the stored procedure. ...... Int ipage = 1; SqlCommand cmd = new SqlCommand ("sp_page", conn ); Cmd. CommandType = CommandType. StoredProcedure; Cmd. Parameters. Add ("@ strTable", "myUser "); Cmd. Parameters. Add ("@ strColumn", "UserId "); Cmd. Parameters. Add ("@ intColType", 0 ); Cmd. Parameters. Add ("@ intOrder", 0 ); Cmd. Parameters. Add ("@ strColumnlist ","*"); Cmd. Parameters. Add ("@ intPageSize", 15 ); Cmd. Parameters. Add ("@ intPageNum", iPage ); Cmd. Parameters. Add ("@ strWhere ",""); SqlParameter paramPageCount = cmd. Parameters. Add ("@ intPageCount", SqlDbType. Int ); ParamPageCount. Direction = ParameterDirection. Output; // You can change it to ExecuteReader () Cmd. ExecuteNonQuery (); Response. Write (paramPageCount. Value. ToString ());