About the basics of getting started with SQL stored procedures (basic knowledge) _mssql

The university in the face of stored procedures did not say anything, working for a while, on the stored procedures or no use, there is no need to write stored procedures, may be the reason for the direction of the software. In order to develop later, the decision starts from the zero to study.

Here's a look at the definition of a stored procedure,

A stored procedure (Stored Procedure) is a set of SQL statements that is compiled and stored in a database to complete a specific function, and the user specifies the name of the stored procedure and gives the parameter if the stored procedure is executed with parameters.

In the series version of SQL Server, stored procedures fall into two categories: system-supplied stored procedures and user-defined stored procedures.

The system SP, which is primarily stored in the master database, is prefixed with sp_ and the system stored procedures primarily obtain information from the system tables to administer SQL Server for the system administrator.

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_stored_procedures;
exec sp_helptext ' sp_stored_procedures ';--view stored procedure creation, definition statements
EXEC sp_rename Student, stuinfo;--modify the name of the table, index, column
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;

User-defined stored procedures are created by the user and can perform a specific function, such as a stored procedure that queries the data information required by the user.

Here we look at the benefits of the stored procedure;

(1) repeated use. Stored procedures can be reused to reduce the workload of database developers.

(2) Improve performance. Stored procedures are compiled when they are created and are not recompiled for future use. Normal SQL statements need to be compiled once per execution, so using stored procedures improves efficiency.

(3) Reduce network traffic. The stored procedure is on the server, and when invoked, it is only necessary to pass the name of the stored procedure and the parameters, thus reducing the amount of data transmitted by the network.

(4) security. Parameterized stored procedures can prevent SQL injection attacks and can apply grant, deny, and revoke permissions to stored procedures.

Okay, let's take a look at the basic syntax created

Copy Code code as follows:

Defining the syntax of a stored procedure

CREATE proc[edure] Stored procedure name

@ Parameter 1 Data type = default value,

...... ,

@ parameter n data type OUTPUT

As

SQL statement

Go
, the parameters are optional
, the parameters are divided into input parameters, output parameters
, the input parameter allows a default value

Here to create a simple stored procedure

CREATE PROCEDURE Userlogin
@name varchar (20),
@password varchar (20)

As

--Defines a variable that is temporarily used to save a password
--declare @strPwd NVARCHAR (20) The variable is not introduced here. Later articles will be discussed in detail
BEGIN
SELECT * from UserInfo where username= @name and userpass= @password
End
Go

First we use a simple SQL query

SELECT * from userinfo where username= ' admin '

Query results:

---------------------

UserName Userpass

Admin Admin

Now we're going to execute our stored procedure.
EXEC userlogin Admin,admin

--or This call:
EXEC userlogin @name = ' admin ', @password = ' admin '

Query results:

---------------------

UserName Userpass

Admin Admin

Note that in SQL Server, all user-defined variables begin with "@", and the OUTPUT keyword indicates that the parameter is used for outputting, followed by the stored procedure content. As soon as the above code is executed in Query Analyzer, SQL Server creates a stored procedure named "Userlogin" in the current database. You can open "Enterprise Manager", select the database of the current operation, and then select "Programmable-> stored Procedures" in the tree list on the left, where you can see the stored procedure you just created in the list on the right (if not, refresh it).

There are two ways to call a stored procedure in your data (here exec is equivalent to execute);

EXEC Process Name parameter value 1, parameter value 2, ....

Or

EXEC parameter 1 = parameter value 1, parameter 2 = parameter value 2 ....

We've seen it above.

If you want to delete a stored procedure, use the drop

Like this

Copy Code code as follows:

Drop PROCEDURE Userlogin

What is the use of creating such a stored procedure, instead of just viewing the game in the data?

We are doing a Web or WinForm program, assuming that we need a login, OK, we can invoke this stored procedure to log in, according to the parameters passed in, if the query out of the record, then this record in the database, indicating that the login succeeded, or failure.

This is done in a more secure way to prevent SQL injection.