T-SQL (stored procedure) for the database family

What is a stored procedure

A stored procedure is a function that is stored in a database and can be called by an application or other stored procedure.

What is stored procedures used for?

Used to implement business logic (especially for businesses that require transactions).
1 advantages
Reduce network traffic
Faster execution times
Stronger adaptability (sometimes the release system does not need to update the client)
Distributed work
Reduce the load on the client
2 Disadvantages
Increase the load on the server

Common system stored procedures

System Stored Procedure Description
Sp_help for viewing object information
sp_helpdb information for querying the database
Sp_helpconstraint viewing constraints on a table
Sp_helpindex viewing the index of a table
Sp_databases is used to display information about all databases, such as database name and data size.
Sp_renamedb changing the name of a database
Sp_rename used to change user-created object names, such as data tables, fields, indexes, and so on in the current database
Sp_tables returning data tables and views in the current database
Sp_columns return column information for a datasheet or view
Sp_password Add or modify a password for a login account

Calling system stored Procedures

EXEC sp_databases--Lists all databases in the current system
Use Booksmanager
Go
EXEC sp_tables--list all data tables and views in database Booksmanager
EXEC sp_columns Books--Lists column information for book tables
EXEC sp_help Books--View all the information in the Book table
EXEC sp_helpconstraint Books--View the constraints of the Book table
EXEC sp_helpindex Books--View the index of the Book table

Extended stored Procedures

An extended stored procedure (Extended stored procedured) is a call to a dynamic-link library (DLL) function.
Extended stored procedures are typically prefixed with "xp_".

User-defined stored procedure 1 stored procedure with no parameters

(1) syntax
CREATE PROCEDURE Stored Procedure name
As
.........
.........
.........
Recommendation: The name of a custom stored procedure should start with USP_
(2) Calling a stored procedure with input parameters
EXEC Stored Procedure Name

2 Stored procedures with parameters

(1) syntax
CREATE PROCEDURE Stored Procedure name
@ Parameter 1 data type [= default value],
......
@ parameter n data type [= default value]
As
.........
.........
.........
(2) Calling a stored procedure with input parameters
Way One:
EXEC usp_score_byparam ' SQL Server BASIC programming ', 70
Way two:
exec usp_score_byparam @coursename = ' SQL Server BASIC Programming ', @pass =70

3 stored procedure with output parameters

(1) syntax
CREATE PROCEDURE Stored Procedure name
@ Parameter 1 data type [= default value],
......
@ parameter n data type OUTPUT
As
.........
.........
.........
(2) Calling a stored procedure with an output parameter
DECLARE @result int
exec usp_add 20,30, @result output
print ' Operation result: ' +str (@result, 5)

T-SQL (stored procedure) for the database family