创建存储过程

create table cunchu
(
id int,
age int,
score int,
)
insert into cunchu values
(1,20,80)
insert into cunchu
select 2,25,85
union
select 3,30,90
union
select 4,35,95

select*from cunchu

go
create procedure p_omg
as
select avg(score) as ‘平均分噢’from cunchu—–普通t_sql语句
select * from cunchu where age>25————–普通t_sql语句

exec p_omg

go
create procedure p_omgd
@zdy int
as
select avg(score) as ‘平均分噢’from cunchu
select * from cunchu where age>@zdy

exec p_omgd @zdy=25
exec p_omgd 25————————-如果变量多要注意顺序

go
create procedure p_omgdo
@zdy int = 30
as
select avg(score) as ‘平均分噢’from cunchu
select * from cunchu where age>@zdy

exec p_omgdo @zdy=25——————-赋值大于给定的
exec p_omgdo 25————————赋值大于给定的
exec p_omgdo—————————不加说明就是给定的30

if exists (select *from sysobjects where name =’bianhao’)
drop table bianhao
create table bianhao
(orderID varchar(20),
type varchar(20),
price int,
date datetime)
insert into bianhao
select ‘2018-5-25-001’,’华为’,2000,’2018-5-25 00:00:00’——00:00:00.000自动转换
union
select ‘2018-5-25-002’,’苹果’,4000,’2018-5-25 00:00:00’
union
select ‘2018-5-26-001’,’三星’,6000,’2018-5-26 00:00:00’
union
select ‘2015-5-27-001’,’oppo’,3000,’2015-5-27 00:00:00’

select*from bianhao

go
create procedure p_omgdom————动态添加存储过程——增删改`查
@orderID varchar(20)=null,———–为null就可以不填数据
@type varchar(20)=null,
@price int=null,
@date datetime=null
AS
if(@orderID is null)
begin
print ‘orderID不能为空’
return
end
insert into bianhao values
(@orderID,isnull(@type,’华为’),isnull(@price,3000),@date)——@xx相当于一个中介

exec p_omgdom @orderID=12345
exec p_omgdom 123456,’小米’,10000

select*from bianhao

go
create procedure p_omgdomg——-动态查询存储过程select换成delete就是删除语句
@orderID varchar(20)=null,
@type varchar(20)=null,
@price int=null,
@date datetime=null
AS
select * from bianhao
where(@orderID is null or orderID=@orderID)
and(@type is null or type=@type)
and(@price is null or price=@price)
and(@date is null or date=@date)

exec p_omgdomg @type=小米

select*from bianhao where type =’小米’

go
alter procedure p_omgdomgd——-动态修改存储过程
@orderID varchar(20)=null,
@type varchar(20)=null,
@price int=null,
@date datetime=null,

@typeNew varchar(20)=null,
@priceNew int=null,
@dateNew datetime=null
as
update bianhao
set type=isnull(@typeNew,type),———等于新值如为空等于旧值(理解)
price=isnull(@priceNew,price),
date=isnull(@dateNew,date)
where(@orderID is null or orderID=@orderID)
and(@type is null or type=@type)
and(@price is null or price=@price)
and(@date is null or date=@date)

exec p_omgdomgd @orderID=’12345’,@priceNew=2989,@typeNew=’苹果’

select * from bianhao

select * from(
select top 4 * from(
select top 6 * from bianhao——-取后四条
order by date)as A
order by A. date desc)as B
order by B.date