存储过程基础
2010-12-03 09:12 bingcaihuang 阅读(236) 评论(0) 编辑 收藏 举报--------------------------------------------------------------------------------
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