Introduction to stored procedures in SQL Server databases _mssql

What is a stored procedure

If you're in touch with other programming languages, it's easy to understand that stored procedures are like methods.

He's the way he is. Then he has a similar method name, a method to pass variables and return results, so stored procedures have stored procedure names, stored procedure parameters, and return values.

Advantages of stored procedures:

The ability of the stored procedure greatly enhances the functionality and flexibility of the SQL language.

1. Ensures data security and integrity.
2. Through stored procedures, users without permission can access the database indirectly under control, thus ensuring the security of the data.
3. Stored procedures allow related actions to occur together to maintain the integrity of the database.
4. Before running the stored procedure, the database has been syntactically and syntactically analyzed, and the optimal execution scheme has been given. This compiled process 5 can greatly improve the performance of SQL statements.
6. Can reduce the traffic of the network.
7. The operational procedures that embody enterprise rules are 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 Procedures

Let's take a look at the system stored procedures, the system stored procedures are defined by the system, mainly in the master database, the name begins with "SP" or begins with "XP". Although these system stored procedures are in the master database,

But 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:

Copy Code code as follows:

exec sp_databases; --View the database
exec sp_tables; --View Table
EXEC sp_columns student;--view columns
EXEC sp_helpindex student;--View index
EXEC sp_helpconstraint student;--constraint
exec sp_helptext ' sp_stored_procedures ';--View the statement of the stored procedure creation definition
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 ';--Change the default database for login names
EXEC sp_helpdb;--database help, querying database information
exec sp_helpdb master;
EXEC sp_attach_db--Additional database
EXEC sp_detach_db--Detaching a database

Stored procedure syntax:

Before you create a stored procedure, first of all, the name of the stored procedures, see several stored procedures are like to create a stored procedure when adding a prefix, to develop in the stored procedure name prefix before the habit is very important, although this is only a very small thing, but often small details determine success or failure. See some people like this prefix, such as proc_ name. Also see this plus-like prefix usp_ name. The former proc is a shorthand for procedure, and the latter sup means user procedure. I prefer the first one, so all of the following stored procedure names are written in the first form. As for the name of the use of the camel naming method.

The syntax for creating stored procedures is as follows:

Copy Code code 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 [parameters]

To use a stored procedure instance:

1. With no parameters

Copy Code code as follows:

CREATE PROCEDURE proc_select_officeinfo--(stored procedure name)
As select Id,name from office_info--(SQL statement)

EXEC proc_select_officeinfo--(Invoke stored procedure)

2. With input parameters

Copy Code code as follows:

CREATE PROCEDURE Procedure_proc_getoffinfobyid--(stored procedure name)
@Id int--(parameter Name argument type)
As select Name from dbo. Office_info where id= @Id--(SQL statement)

EXEC Procedure_proc_getoffinfobyid 2--(after stored procedure name, spaces plus arguments, multiple arguments separated by commas)

Note: Parameter assignment is, the first parameter can not write the parameter name, after passing in the parameter, need to explicitly pass to which parameter name

3. With input and output parameters

Copy Code code as follows:

CREATE PROCEDURE proc_office_info--(stored procedure name)
@Id int, @Name varchar output--(parameter Name argument type) outgoing parameter to add output
As
Begin
Select @Name =name from dbo. Office_info where id= @Id--(SQL statement)
End
DECLARE @houseName varchar (20)--Declares a variable, gets the value of the stored procedure
EXEC proc_office_info--(stored procedure name)
4, @houseName output--(legend parameter to add output this side if the @ variable = output will be an error, so another way of writing)
Select @houseName-(Display values)

4. With the return value of the

Copy Code code as follows:

CREATE PROCEDURE proc_office_info--(stored procedure name)
@Id int--(parameter Name argument type)
As
Begin
If (select Name from dbo. Office_info where id= @Id) =null--(SQL statement)
Begin
Return-1
End
Else
Begin
Return 1
End
End

DECLARE @house varchar (20)--Declares a variable, gets the value of the stored procedure
EXEC @house =proc_office_info 2--(call stored procedure, receive return value with variable)
Note: A stored procedure with a return value can only be a return value of type int
Print @house