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

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'