SQL Server Stored Procedure example

I. Problems Encountered

We have written a lot of batch processing statements in our previous courses. These batch processing statements have two problems:

1. Unable to pass parameters like functions (custom)

2. It cannot be called repeatedly like a function (functional modularization)

Speaking of this, we can guess that database objects in the form of functions should be created in the database to solve this problem. But before introducing such database objects, let's look at several issues that need to be solved:

We have learned to encapsulate a select statement in a view, but it can only be used for queries. If we want to perform other operations, such as adding, deleting, modifying, and deleting database tables, views cannot be used.

On the other hand, if you store a batch with a change operation as a database object, you can also move the data processing program to the nearest possible place to the data, instead of writing these operations in client programs (such as Java and C. By moving data processing programs from customer applications to servers, you can reduce network traffic and improve performance and data integrity.

Ii. Concept of Stored Procedure

To solve the problem above, we can use a database object called "stored procedure.

Stored Procedure saves a string of complex SQL statements that we often use as a database object and gives it a name. Each time you use a stored procedure, you only need to use the following format:

Exec proc stored procedure name

Stored procedures can also run with parameters:

Exec proc stored procedure name parameter value [, parameter value...]

? The stored procedure is not mysterious. It is batch processing. As mentioned above, a view is a named select statement stored on the server. Similarly, a stored procedure is a named batch process stored on the server. The system will compile it in advance.

? Stored Procedures can contain almost all of the T-SQL statements, such as data access statements, flow control statements, error processing statements, etc., the use of elastic.

? There are also two types of objects in the database: system functions and user-defined functions. The functions of user-defined functions are similar to those of stored procedures, but there are some differences.

[Storage process classification]

-System stored procedures: prefix of system stored procedure sp _ such as sp_help sp and helpdb

-Extended stored procedure prefix xp _ for example xp_mongoshell

-User-defined stored procedure, which is created by ourselves.

3. create, modify, and delete a custom Stored Procedure

[Create a stored procedure]

Create proc stored procedure name-a stored procedure without Parameters

As

Batch statement

Go

[Modify stored procedure]

Alter proc stored procedure name-a stored procedure without Parameters

As

Batch statement

Go

You can see that the syntax for modifying a stored procedure is only one word different from the syntax for creating a stored procedure. Replace "create" with "alt.

Delete A Stored Procedure]

Drop proc stored procedure name

You can also use management studio to manage stored procedures. Expand the "programmability" in the menu tree and perform various operations on the sub-nodes of the "stored procedure. Here, let's talk about creation: After clicking "Create a stored procedure", a template-based creation statement will appear. Click "query> specify template parameter value" in the menu to bring up a dialog box to set the template and create the stored procedure. In addition, click "View> template resource manager" in the menu to view the various SQL statement templates provided by SQL SERVER.

[A simple example]

-- Insert a user with time as the user name

Create proc insUser

As

Begin tran

Declare @ username varchar (20)

Set @ username = convert (varchar (8), getdate (), 112)

+ Replace (convert (varchar (10), getdate (), 8 ),':','')

If not exists (select * from yonghu where yonghuming = @ username)

Insert into yonghu values

(@ Username, '000000', '@ 163.com', 'new user ')

Commit tran -- you can also write commit, but it is recommended not to remove tran

Go

Then execute the stored procedure using exec:

Exec insUser

Select the exec line and press F5 to execute it repeatedly. You will find that only one user can be inserted in the same second.

Once the stored procedure is created, the Code cannot be executed again. You can change create to alter to modify it.

Note that you cannot use the go statement in the Stored Procedure Code, because go is used to submit batches. Once the go system encounters a problem, the code of the stored procedure has been written, will submit the batch processing of create or alter. If you want to execute another batch in the stored procedure, write the batch as another stored procedure and call it.

Iv. User-Defined stored procedure parameter transfer and Return Value

[Transfer Parameters]

Create | alter proc stored procedure name

@ Parameter name parameter type [,

@ Parameter name parameter type...]

As

Batch statement

Go

Do you still remember the previous case?

Declare @ tablename nvarchar (10), @ id varchar (10), @ idvalue int

Declare @ SQL varchar (100)

Set @ tablename = 'yilen'

Set @ id = 'yirenid'

Set @ idvalue = 10

Set @ SQL = 'select * from' + @ tablename + 'where' + @ id + '=' + cast (@ idvalue as varchar)

Print @ SQL

Exec (@ SQL)

Now we write it as a stored procedure. In this way, we can extract the expected records from a specified table every time.

Create proc queryItem

@ Tablename nvarchar (10 ),

@ Id varchar (20 ),

@ Idvalue int -- the parameter can be enclosed in parentheses to make it clearer.

As

Declare @ SQL varchar (100)

Set @ SQL = 'select * from' + @ tablename +'

Where '+ @ id +' = '+ cast (@ idvalue as varchar)

Exec (@ SQL)

Go

Call method:

Exec queryItem 'yirenid', 'yirenid', @ idvalue = 10

QueryItem 'jingjiren', 'jingjirenid', 1

Stored procedures can be called without exec, but this is not recommended. The name of the stored procedure parameter can be written during the call, but this is completely unnecessary, so @ idvalue = 10 can be directly written as 10.

The effect of adding exec with no parentheses is different. Parentheses are used to execute SQL statements, and without parentheses, they are used to execute stored procedures.

[Return value]

1. Return with retrun, always an integer

Return can only return integers. Even if a statement such as "return integer" is not explicitly written, a value 0 is automatically returned for the stored procedure, indicating that the operation is successful. We can return a non-0 value when an error occurs, indicating that an error has occurred. Do not try to use return to return a result processed in the stored procedure, such as name or birthday, because it is an integer and has very limited functions. We only need to use it to return the execution status of the stored procedure. See the example below:

Create proc returnProc

As

Begin tran

Declare @ error int

Insert into yiren (xingming) values ('wang mei ')

Set @ error =@@ error

Insert into yiren (yirenid) values (1)

Set @ error = @ error + @ error

If @ error> 0

Rollback tran

Else

Commit tran

Return @ error

Go

Call method:

Declare @ error int

-- Set @ error = exec returnProc -- This write is wrong ......

Exec @ error = returnProc

Select 'Return value' = @ error

If returnProc has a parameter @ xingming that you want to pass in 'wang mei', you can call it like this:

Exec @ error = returnProc 'wang mei'

-- Or:

Exec @ error = returnProc @ xingming = 'wang mei'

2. return data with the output parameter

Output can be used to return any type of data. Strictly speaking, it is not a "return value", but an "external variable" that can be seen in the code of the stored procedure call ". The reason for this is as follows:

-- Query artist name and age by id

Create proc queryProfile

@ Id int,

@ Xingming varchar (50) output, -- output is required.

@ Nianling int output

As

Select @ xingming = xingming, @ nianling = nianling

From yiren where yirenid = @ id

Go

Call method:

Declare @ xingming varchar (50), @ nianling int

Exec queryProfile 1, @ xingming output, @ nianling output -- output must exist.

Print 'artist name 1 is '+ @ xingming

+ ', Age:' + cast (@ nianling as varchar) + 'Year'

In the call, we first defined two variables, and then passed the two variables to the stored procedure in the form of output. So the stored procedure can see these two variables from the external. All modifications to these two variables in the stored procedure can be immediately reflected in the Code at the call, because they are actually two variables at the call.

3. select statement result set

If the select statement is executed in the stored procedure and the result set is displayed (instead of assigning values to variables using the select statement ), this result set can also be considered as a return value (it cannot be used by batch processing statements, but can be used as a result set by C # and other programming languages ).

Create proc selectProc

As

Select * from yiren

Go

Exec selectProc

This stored procedure can be used to implement a "view with Parameters". The example above [passing parameters] is.

[Common functions of stored procedures]

From the usage of stored procedure parameters and return values, we can see that stored procedures are usually used to process database update operations or query information from the database according to specific needs, it is returned to the call site in the form of a variable (rather than a result set) or a result set, but cannot be used by the statements at the call. We can use the return value to monitor whether the stored procedure runs smoothly.

5. nested calling of Stored Procedures

In a stored procedure, You can execute another stored procedure, namely, nested call. Nesting can be performed multiple times, but a maximum of 32 layers are allowed. You can use @ NESTLEVEL to view the current number of nested layers:

Create proc proc_1 -- get artist id

@ Yid int output

As

Select top 1 @ yid = yirenid from yiren

Where nicheng = 'Sister Furong'

Print 'stored procedure 1 in '+ cast (@ NESTLEVEL as varchar) +'

Go

Create proc proc_1_1 -- get the fan id

@ Fid int output

As

Declare @ yid int

Exec proc_1 @ yid output

Select @ fid = yonghuid from fensi

Where yirenid = @ yid

Order by yonghuid desc

Print 'stored procedure 1_1 in '+ cast (@ NESTLEVEL as varchar) +' level'

Go

Create proc proc_1_1_1 -- get a fan of Sister Furong

As

Declare @ fid int

Exec proc_1_1 @ fid output

Select * from yonghu

Where yonghuid = @ fid

Print 'stored procedure 1_1 in '+ cast (@ NESTLEVEL as varchar) +'

Go

Exec proc_1_1_1

Go

6. system stored procedures

The system stored procedure is a stored procedure created by the SQL SERVER system. It is used to conveniently query information or complete system management tasks. The common system sp is as follows (for more information, see books online)

Sp_databases: list all databases on the server (No parameter)

Sp_server_info: List Server Information (parameters can be set)

Sp_stored_procedures: list all stored procedures in the current environment (parameters can be set)

Sp_tables: return the list of objects that can be queried in the current environment (No parameter)

Sp_configure displays or changes the global configuration settings of the current server

Sp_help displays information about database objects (which can have parameters), for example, sp_help yiren.

Sp_helpdb displays information about the database (which can have parameters), such as sp_helpdb SuperStar.

Sp_helptext displays the text (with parameters) of rules, default values, unencrypted stored procedures, user-defined functions, triggers, or views, such as sp_helptext proc_1

Sp_renamedb rename the database (with parameters), for example, sp_renamedb 'pubs' and 'publisher'

7. Precautions for Stored Procedures

[Advantages of stored procedures]

? Compilation only at creation, fast execution Speed and High Efficiency

? Reduces network transmission traffic

? Improve Security

? Modular programming, which can be reused

? Unify each operation process

[Secretly speaking]

No matter what you learn, you should mention the disadvantages, but the stored procedure is not mentioned here. This does not mean that the stored procedure has no disadvantages, after all, nothing can be abused, but it can be seen that the stored procedure is indeed the core content of T-SQL programming, is the most important part. And it is so easy to grasp, I believe you are in a good mood, right?

[Use transactions in the stored procedure]

Transactions can be used in the stored procedure, which is undoubtedly true. When using transactions in a stored procedure, you do not need to use the goto statement. You can directly return the rollback or commit statement to terminate the execution of the stored procedure. As mentioned above, return statements can also be used in normal batch processing.

8. Implement paging query using Stored Procedures

Create proc queryPage @ tablename nvarchar (50), -- used to input the table name @ idname nvarchar (50), -- used to input the field name @ pagesize int, -- used to input the number of records per page @ currentpage int, -- used to input the page number you want to view @ totalpages int output -- used to output the total number of pages as -- declared to save the local variable of the query statement: declare @ SQL as nvarchar (1000) -- local variable that declares the total number of records to be saved: declare @ rowcount as int -- total number of records obtained: set @ SQL = 'select @ rc = count (*) from '+ @ tablename -- do not directly execute select @ rowcount = count (*) from @ tablename -- input the parameter statement: exec sp_executesql @ SQL, n' @ rc int output ', @ rc = @ rowcount output -- saves the total number of pages per page to the output parameter: set @ totalpages = ceiling (cast (@ rowcount as float) /cast (@ pagesize as float )) if @ currentpage> 1 begin if @ currentpage> @ totalpages begin set @ currentpage = @ totalpages -- the end set @ SQL = 'select top' + cast (@ pagesize varchar) + '* from' + @ tablename + 'where' + @ idname +' not in (select top '+ cast (@ pagesize * (@ currentpage-1) as varchar) + ''+ @ idname + 'from' + @ tablename + 'ORDER BY' + @ idname + ') order by '+ @ idname endelse -- select only the first page and you do not need to use subqueries. This improves the performance of begin set @ SQL = 'select top' + cast (@ pagesize as varchar) + '* from' + @ tablename + 'ORDER BY' + @ idnameendexec (@ SQL) -- execute the query statement go

? Sp_executesql is another method for executing SQL statements. It is more advanced than exec (@ SQL. Here we will input three parameters for it, namely @ SQL (which must be nvarchar type) waiting for processing) use the string to define a new variable name for the @ SQL statement and assign values to the new variable defined in the string (@ rc is the new variable defined in the string and assigns it to @ rowcount, because you want to return the value through @ rc for @ rowcount, specify it as output ).

? The ceiling function returns the smallest integer greater than a decimal number. For example, ceiling (3.5) returns 4. Here, @ rowcount and @ pagesize are separated, and the resulting number is a decimal number. The fractional part cannot form a complete page, but it does not mean that there is no record. Therefore, use the ceiling function to assign values to @ totalpages correctly.

? When the incoming currentpage is not within the correct page number range, our code processes it and assigns @ currentpage a value within the correct range.

Call the above stored procedure:

Declare @ totalpages int

Exec queryPage 'yirenid', 'yirenid', 5, 13, @ totalpages output

Print 'Total' + cast (@ totalpages as varchar) + 'row'