sql server stored procedure
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:
----- --- 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! ' Endendgoexec 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 shippedasbegin 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! ' Endendgoexec chuhuo 'Apple',11---------------------------Delete one Notebook data -------create proc qingchu@scbjbn nvarchar(20) --The name of the notebook to be deletedasbegin 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~~'endexec qingchu 'Apple'

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

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 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.

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 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.

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

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 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

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.
