SQL Server Stored Procedure example

I. Some batch processing statements have been written in the previous course. These batch processing statements have two problems: 1. parameters cannot be passed for running (customized) Like functions. 2. there is no way to call it repeatedly like a function (functional modularization). Here we can guess that the database should be able to create database objects in the form of functions to solve this problem.

I. Some batch processing statements have been written in the previous course. These batch processing statements have two problems: 1. parameters cannot be passed for running (customized) Like functions. 2. there is no way to call it repeatedly like a function (functional modularization). Here we can guess that the database should be able to create database objects in the form of functions to solve this problem.

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'