VB.net database programming (05): SQL Server Stored Procedure

SQL Server Stored Procedure

To put it bluntly, it is a batch processing process.

It means that a bunch of SQL statements are executed continuously, which may contain some variables, process control, and display.

Therefore, first define a stored procedure with a batch of processing names, and then call it with some parameters.

use Salesgocreate procedure demo_proc(@name char(16)='SQL Server',@major int=2005,@minor int=0)as    print @name + STR(@major,5)+'.'+STR(@minor,5)

The variable starts with @, and @ name char (16) = 'SQL Server' is the variable name, type char, 16 characters long, default value: SQL Server.

The following print indicates that STR converts the data word to a string and the total length (including the decimal point) is 5.

The second step is to execute it:

When the parameters are not followed, they are executed according to the original parameter style (default value.

The following parameters are displayed by parameters. default is the original default value. If no parameter is specified, the default value is used.

Create a stored procedure and execute

-- Use salesgocreate procedure getstudentcount @ v_math intasprint: 'select count (*) from grade where mathematics> @ v_math -- The following is the call process: Execute getstudentcount 60

The result is:

Another example of Stored Procedure

Sometimes the user name and password are saved in a table.

When the user comes in, perform verification.

Take a look at the stored procedure:

-- Create a table with a user name and password first use salesgocreate table webusers (username varchar (20), userpass varchar (10) insert into webusers values ('A', 'A ') insert into webusers values ('bb ', 'bb') -- then, create procedure sp_checkpass (@ chkname varchar (30 ), @ chkpass varchar (30), @ isvalid varchar (12) Output) Asif exists (select username from webusers where username = @ chkname and userpass = @ chkpass) select @ isvalid = 'good' else select @ isvalid = 'bad' -- test the Stored Procedure declare @ AA varchar (12) exec sp_checkpass 'A', 'A ', @ AA outputselect @ AA as 'Return value'

Note: The above output is used for output.

Official explanation: return information in the rows affected by insert, update, delete, or merge statements, or return expressions based on the rows affected by these statements.

 
These results can be returned to the processing application for use in validation messages, archives, and other similar application requirements. You can also set these results

Insert a table or table variable. In addition, you can capture embedded
Insert, update, delete, or merge statement, and then

Insert these results into the target table or view.

The result is as follows:

The stored procedure is a batch of processing procedures.

However, there are a lot of syntax.