存储过程基础

--------------------------------------------------------------------------------
1.创建存储过程 
create procedure 过程名 as 过程体
例如：
create procedure up_selectionBusnisess as
select title,price
from titles
where type = 'busness'
Go 

 

--------------------------------------------------------------------------------
2.带参存储过程 
create procedure [up_insert]
(@contact_1 [char](3),
@email_1 [varchar] (30))
as
insert into [person].[dbo].[db_contact]([contact],[email])
values(@contact_1,@email_1)
--备注:contact_1,email_1为声明的变量，就是输入参数，分别对应表中各列;
执行:
exec up_insert
@contact_1 = '001',
;@email_1 = 'hunya_2353@126.com'
Go

 

--------------------------------------------------------------------------------
3.带返回值的存储过程 
例如:输入手机号码，输出相应办公室电话号码与住址
create procedure up_search
(@mobile [char] (11),
@phone [char] (13) output,
@address [varchar] (30) output)
as
select @phone = phone;
@address = address
from contact 
where mobile = @mobile
Go
//备注：查询到的电话存储在@phone变量中，地址存储在@address中
执行调用:
declare @phone_1 [varchar] (13)
declare @address_1 [varchar] (30)
EXEC up_search '13896010465',
@phone_1 output,
@address_1 output
print '电话号码:' + @phone_1
print '地 址:' + @address
Go

 

--------------------------------------------------------------------------------
我的例子: 
create procedure ChooseVehicle
(
@LicenseTag nvarchar(30),
@EnRegisterDate smalldatetime output,
@CBIID nvarchar(20) output
)
as
select @EnRegisterDate = EnRegisterDate,
@CBIID = CBIID
From VehicleInfo 
Where licensetag = @LicenseTag
go 

declare @EnRegisterDate smalldatetime
declare @CBIID nvarchar(20) 
exec choosevehicle'渝A92093',
@EnRegisterDate output,
@CBIID output
print '注册时间:' + convert(char(10),@EnRegisterDate,120)
print 'CBIID:' + @CBIID