Home Database Mysql Tutorial sql server stored procedure

sql server stored procedure

Dec 03, 2016 am 10:23 AM
server sql storage

Stored Procedures

[Create is to create a stored procedure, alter is to modify and change a stored procedure]

[Use create when writing a stored procedure for the first time. If you modify the stored procedure program, replace create with alter and then execute]

[In the database, begin and end mean braces]

·Format for creating stored procedures:

--(procedure can be abbreviated as proc) proc means program and step. Followed by the stored procedure name

create proc The stored procedure name

as

Code block

Go

--exec means execution. Execute stored procedure

Exec Stored procedure name

---------Modify stored procedure
alter proc hehe ---alter means change, change
as
select student number, Chinese score from fenshu
go
exec hehe

-------------Query multiple tables

create proc chaxun

as

begin

select * from fenshu

select * from jiaoshi

select * from xuesheng

end

go

exec chaxun

--------------Stored procedure with parameters

create proc chucunguocheng

@yican varchar(20), @yican means formal parameter

@ercan varchar(20)

as

begin

print @yican+@ercan

end

go

exec chucunguocheng 'Hello','China'

Example question:

-------Enter the student number to determine whether the student is excellent, graduated, or not (passing three courses is considered excellent, passing two courses is considered completed)

alter proc biye

@xuehao int — Create input variables

as

begin

declare @y int

declare @s int

declare @w int

declare @zongshu int

select @y=COUNT(*) from fenshu where student ID=@xuehao and Chinese score>=60

select @s=COUNT(*) from fenshu where student number=@xuehao and math score>=60

select @w=COUNT(*) from fenshu where student number=@ xuehao and English score>=60

set @zongshu=@y+@s+@w

if @zongshu=3

​​​ print 'Excellent'

if @zongshu =2

​​​ print 'Complete'

if @ zongshu=1

  print'Not completed'

if @zongshu=0

  Print''Input error'

end

go

exec biye 1

The result is:

sql server stored procedure

----- --- Comprehensive exercises

(Comprehensive training of stored procedures)

Create a goods table: number, goods name, unit, price, inventory quantity, remarks. (10 pieces of data) After

, purchase the goods. If the goods already exist, increase the quantity. Otherwise, add them to the database table.

Shipping, if someone wants the goods, judge whether the quantity is sufficient and reduce the inventory enough, otherwise we will inform you of the shortage.

Delete the data in the database at any time based on the name. If there is any, delete it. If not, we will notify you.

------------Create database and data table, and insert data----------

create database notebook

go

create table bijiben

(

Number int,

Name nvarchar(20),

Remarks varchar(20),

Price int,

Inventory int,

Unit nvarchar(10)

)

go                                                                                     to -----(Random ranking)------

insert into bijiben values(1,'Apple','macbook',12000,10,'United States')

insert into bijiben values(2,' Acer','acer',3500,20,'China Taiwan')

insert into bijiben values(3,'Asus','asus',3500,25,'China')

insert into bijiben values(4, 'Dell','dell',4300,30,'United States')

insert into bijiben values(5,'Shenzhou','hass',4000,20,'China')

insert into bijiben values(6, 'Lenovo','lenovo',4200,30,'China')

insert into bijiben values(7,'HP','ph',3600,20,'United States')

insert into bijiben values(8, 'Samsung','samsung',3700,10,'Japan')

insert into bijiben values(9,'Sony','sony',7000,10,'Japan')

insert into bijiben values(10, 'Toshiba','toshiba',3200,10,'Japan')

select *from bijiben

-------------------------Incoming stock- -----------------------

create proc jinhuo --Create purchase stored procedure

@bianhao int, --Purchasing number

@bjbn nvarchar( 20),--notebook name

@beizhu nvarchar(20),--remarks

@jiage int,--price

@jinhuo int,--how many units

@danwei nvarchar(20)--unit

as

begin

declare @ybjbn nvarchar(20),@ykc int --@ykc is the original inventory number in the data

select @ybjbn=count(name) from bijiben where name=@bjbn

if @ybjbn=0 - -When there is no entered data in the database

begin

insert into bijiben values(@bianhao,@bjbn,@beizhu,@jiage,@jinhuo,@danwei)

print'New computer added successfully! '

end

else if @ybjbn=1 --When there is input data in the database

begin

select @ykc=stock from bijiben where name=@bjbn

set @ykc=@ykc+@jinhuo

B Update bijiben set inventory =@ykc where name =@bjbn

prop' This computer inventory is successfully added! '

End

end

go

exec jinhuo 11,'Dell','dell',4200,10,'United States'

---------------- -----Shipping--------------------------------

create proc chuhuo --Create shipping stored procedure

@name nvarchar(20 ), --The name of the notebook to be shipped

@shuliang int --The quantity to be shipped

as

begin

declare @ygeshu int,@hgeshu int --@ygeshu is the original inventory of the database, @hgeshu transaction The remaining inventory after

select @ygeshu=stock from bijiben where name=@name

if @shuliang>@ygeshu --When the quantity shipped is greater than the quantity in stock

print 'Sorry, insufficient stock~~'

else

begin

set @hgeshu=@ygeshu-@shuliang

update bijiben set inventory=@hgeshu where name=@name --modify the inventory number after the transaction

print 'Transaction successful! '

End

end

go

exec chuhuo 'Apple',11

---------------------------Delete one Notebook data -------

create proc qingchu

@scbjbn nvarchar(20) --The name of the notebook to be deleted

as

begin

declare @sgeshu int --The individual of the notebook to be found Count

select @sgeshu=COUNT(*) from bijiben where name=@scbjbn

if @sgeshu=1

begin

delete from bijiben where name=@scbjbn

print'The notebook's data was deleted successfully! '

end

if @sgeshu=0

print 'No notebook with this name found~~'

end

exec qingchu 'Apple'


Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What is the difference between HQL and SQL in Hibernate framework? What is the difference between HQL and SQL in Hibernate framework? Apr 17, 2024 pm 02:57 PM

HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

Usage of division operation in Oracle SQL Usage of division operation in Oracle SQL Mar 10, 2024 pm 03:06 PM

"Usage of Division Operation in OracleSQL" In OracleSQL, division operation is one of the common mathematical operations. During data query and processing, division operations can help us calculate the ratio between fields or derive the logical relationship between specific values. This article will introduce the usage of division operation in OracleSQL and provide specific code examples. 1. Two ways of division operations in OracleSQL In OracleSQL, division operations can be performed in two different ways.

Comparison and differences of SQL syntax between Oracle and DB2 Comparison and differences of SQL syntax between Oracle and DB2 Mar 11, 2024 pm 12:09 PM

Oracle and DB2 are two commonly used relational database management systems, each of which has its own unique SQL syntax and characteristics. This article will compare and differ between the SQL syntax of Oracle and DB2, and provide specific code examples. Database connection In Oracle, use the following statement to connect to the database: CONNECTusername/password@database. In DB2, the statement to connect to the database is as follows: CONNECTTOdataba

What does the identity attribute in SQL mean? What does the identity attribute in SQL mean? Feb 19, 2024 am 11:24 AM

What is Identity in SQL? Specific code examples are needed. In SQL, Identity is a special data type used to generate auto-incrementing numbers. It is often used to uniquely identify each row of data in a table. The Identity column is often used in conjunction with the primary key column to ensure that each record has a unique identifier. This article will detail how to use Identity and some practical code examples. The basic way to use Identity is to use Identit when creating a table.

Huawei will launch innovative MED storage products next year: rack capacity exceeds 10 PB and power consumption is less than 2 kW Huawei will launch innovative MED storage products next year: rack capacity exceeds 10 PB and power consumption is less than 2 kW Mar 07, 2024 pm 10:43 PM

This website reported on March 7 that Dr. Zhou Yuefeng, President of Huawei's Data Storage Product Line, recently attended the MWC2024 conference and specifically demonstrated the new generation OceanStorArctic magnetoelectric storage solution designed for warm data (WarmData) and cold data (ColdData). Zhou Yuefeng, President of Huawei's data storage product line, released a series of innovative solutions. Image source: Huawei's official press release attached to this site is as follows: The cost of this solution is 20% lower than that of magnetic tape, and its power consumption is 90% lower than that of hard disks. According to foreign technology media blocksandfiles, a Huawei spokesperson also revealed information about the magnetoelectric storage solution: Huawei's magnetoelectronic disk (MED) is a major innovation in magnetic storage media. First generation ME

Detailed explanation of the Set tag function in MyBatis dynamic SQL tags Detailed explanation of the Set tag function in MyBatis dynamic SQL tags Feb 26, 2024 pm 07:48 PM

Interpretation of MyBatis dynamic SQL tags: Detailed explanation of Set tag usage MyBatis is an excellent persistence layer framework. It provides a wealth of dynamic SQL tags and can flexibly construct database operation statements. Among them, the Set tag is used to generate the SET clause in the UPDATE statement, which is very commonly used in update operations. This article will explain in detail the usage of the Set tag in MyBatis and demonstrate its functionality through specific code examples. What is Set tag Set tag is used in MyBati

Vue3+TS+Vite development skills: how to encrypt and store data Vue3+TS+Vite development skills: how to encrypt and store data Sep 10, 2023 pm 04:51 PM

Vue3+TS+Vite development tips: How to encrypt and store data. With the rapid development of Internet technology, data security and privacy protection are becoming more and more important. In the Vue3+TS+Vite development environment, how to encrypt and store data is a problem that every developer needs to face. This article will introduce some common data encryption and storage techniques to help developers improve application security and user experience. 1. Data Encryption Front-end Data Encryption Front-end encryption is an important part of protecting data security. Commonly used

How to solve the 5120 error in SQL How to solve the 5120 error in SQL Mar 06, 2024 pm 04:33 PM

Solution: 1. Check whether the logged-in user has sufficient permissions to access or operate the database, and ensure that the user has the correct permissions; 2. Check whether the account of the SQL Server service has permission to access the specified file or folder, and ensure that the account Have sufficient permissions to read and write the file or folder; 3. Check whether the specified database file has been opened or locked by other processes, try to close or release the file, and rerun the query; 4. Try as administrator Run Management Studio as etc.

See all articles