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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.