Shulou(Shulou.com)05/31 Report--

How is the stored procedure in SQL Server database? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

What is a stored procedure?

If you have been exposed to other programming languages, it is easy to understand that stored procedures are like methods.

It turns out that he is a method, so he has a similar method name, the variable to be passed by the method and the return result, so the stored procedure has a stored procedure name, a stored procedure parameter and a return value.

Advantages of stored procedures:

The ability of stored procedures greatly enhances the function and flexibility of the SQL language.

The security and integrity of the data can be guaranteed.

two。 Through the stored procedure, the unauthorized users can access the database indirectly under control, so as to ensure the security of the data.

3. Through stored procedures, related actions can occur together, thus the integrity of the database can be maintained.

4. Before running the stored procedure, the database has analyzed the syntax and syntax of the stored procedure, and given the optimized execution scheme. This compiled process

5. Can greatly improve the performance of SQL statements.

6. Can reduce the traffic of the network.

7. The operation program that embodies the enterprise rules is put into the database server for centralized control.

Stored procedures can be divided into system stored procedures, extended stored procedures and user-defined stored procedures.

System stored procedure

Let's first take a look at the system stored procedures, which are defined by the system and are mainly stored in the MASTER database, with the name beginning with "SP" or "XP". Although these system stored procedures are in the MASTER database

However, we can still call system stored procedures in other databases. Some system stored procedures are automatically created in the current database when a new database is created.

Common system stored procedures are:

The code is as follows:

Exec sp_databases;-View the database

Exec sp_tables;-View tabl

Exec sp_columns student;-- View column

Exec sp_helpIndex student;-- View Index

Exec sp_helpConstraint student;-- constraint

Exec sp_helptext 'sp_stored_procedures';-- view the statements defined by the stored procedure creation

Exec sp_stored_procedures;exec sp_rename student, stuInfo;-- change table name

Exec sp_renamedb myTempDB, myDB;-- change database name

Exec sp_defaultdb 'master',' myDB';-- changes the default database of the login

Exec sp_helpdb;-- Database help

Exec sp_helpdb master;-- query database information

Exec sp_attach_db-attach database

Exec sp_detach_db-detach the database

Stored procedure syntax:

Before creating a stored procedure, let's talk about the naming of the stored procedure. I have seen several articles about stored procedures that like to add a prefix when creating a stored procedure. It is important to get into the habit of prefixing the name of a stored procedure. Although this is only a small thing, small details often determine success or failure. See that some people like to add prefixes like this, such as proc_ name. Also see this plus sample prefix usp_ name. The former proc is an abbreviation for procedure, while the latter sup means user procedure. I prefer the first, so all the stored procedure names below are written in the first. As for the writing of the name, the camel naming method is used.

The syntax for creating a stored procedure is as follows:

The code is as follows:

CREATE PROC [EDURE] stored procedure name

@ Parameter 1 [data type] = [default] [OUTPUT]

@ Parameter 2 [data type] = [default] [OUTPUT]

AS

SQL statement

EXEC procedure name [parameter]

Use a stored procedure example:

1. Without parameters

The code is as follows:

Create procedure proc_select_officeinfo-- (stored procedure name) as select Id,Name from Office_Info-- (sql statement)

Exec proc_select_officeinfo-- (call stored procedure)

two。 With input parameters

The code is as follows:

Create procedure procedure_proc_GetoffinfoById-- (stored procedure name) @ Id int-- (parameter name parameter type) as select Name from dbo.Office_Info where Id=@Id-- (sql statement)

Exec procedure_proc_GetoffinfoById 2Mel-(after the stored procedure name, a space is added to the parameter, and the parameters are separated by a comma)

Note: the parameter assignment is that the first parameter does not have to write the parameter name, and then pass in the parameter. You need to specify which parameter name is passed in.

3. With input and output parameters

The code is as follows:

Create procedure proc_office_info-- (stored procedure name) @ Id int,@Name varchar (20) output-- (parameter name parameter type) add outputas beginselect @ Name=Name from dbo.Office_Info where Id=@Id-- (sql statement) enddeclare @ houseName varchar (20)-- declare a variable to get the value exec proc_office_info-- (stored procedure name) 4 passed from the stored procedure. @ houseName output-- (legend has it that you need to add output to the parameter. If you use the @ variable = OUTPUT here, you will get an error. So put it another way) select @ houseName-- (display value)

4. With return value

The code is as follows:

Create procedure proc_office_info-- (stored procedure name) @ Id int-- (parameter name parameter type) as beginif (select Name from dbo.Office_Info where Id=@Id) = null-(sql statement) beginreturn-1endelsebeginreturn 1endend

Declare @ house varchar (20)-- declare a variable to get the value exec @ house=proc_office_info 2 from the stored procedure-- (call the stored procedure and receive the return value with the variable)-- Note: stored procedures with return values can only be print @ house of type int.

After reading the above, have you mastered the method of stored procedures in SQL Server database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.