(1)--向表中添加一个字段

use tep
go

create proc addd
@idd int,
@name char,
@cityy char,
@phonee int,
@faxx int
as
declare @iddd int
declare @namee char
declare @cityyy char
declare @phoneee int
declare @faxxx int

set @iddd=@idd  
set @namee=@name
set @cityyy=@cityy
set @phoneee=@phonee
set @faxxx=@faxx

insert into customers

values(@iddd,@namee,@cityyy,@phoneee,@faxxx)
go

注释：

set @iddd=@idd  
set @namee=@name
set @cityyy=@cityy
set @phoneee=@phonee
set @faxxx=@faxx

set 后不能有逗号“，”

或者这几个语句改为：

select @iddd=@idd,
 @namee=@name,
 @cityyy=@cityy,
 @phoneee=@phonee,
 @faxxx=@faxx

select 后有逗号

 declare @iddd int
declare @namee char
declare @cityyy char
declare @phoneee int
declare @faxxx int

或者

declare @iddd int,
@namee char,
 @cityyy char,
 @phoneee int,
 @faxxx int

 注意逗号

 (2) 更新表inventory

create proc prUpdateStatus
@inventoryid int,
@statusid int
as
update inventory
set statusid=@statusid
where inventoryid=@inventoryid

（3）多表查询

create procedure prListProperties
@inventoryid int
as

select property.property property,
inventoryproperty.value value,
property.unit unit
from inventoryproperty join property
on inventoryproperty.propertyid=property.propertyid
where inventoryproperty.inventoryid=@inventoryid
go

语法：
Select tableA.a,b,c

From tableA,tableB

Where tableA.a=tableB.a

当两表有相同列名时，需在元素前加表名 tableA.a 。。。

单表内的模拟多表查询：

Select first.a,second.a

From tableA first,tableA second

Where first.b = second.f

（4）带返回值

create proc prGetLeaseScheduleID
@InventoryId int,
@LeaseScheduleId int output
as
select @LeaseScheduleId=LeaseScheduleId
from inventory
where inventoryid=inventoryid

declare @result int
exec prgetleasescheduleid
@leasescheduleid=@result output,    --不要写反，跟其他语言有所不同，赋值
@inventoryid=121

select @result result
 

评论