SQL server 存储过程的建立和调用
存储过程的建立和调用
--1.1准备测试需要的数据库:test,数据表:物料表,采购表
if not exists (select * from master.dbo.sysdatabases where name='test')
create database test
go
use test
go
if object_id('test..物料表') is null
begin
create table 物料表(编号 varchar(6),名称 varchar(40),类别 varchar(20))
insert into 物料表
select 'A00001','46寸电视机','电视机' union all
select 'A00002','52寸电视机','电视机' union all
select 'A00003','60寸电视机','电视机' union all
select 'A00004','39寸电视机','电视机' union all
select 'A00005','16升洗衣机','洗衣机' union all
select 'A00007','美的1匹空调','空调' union all
select 'A00008','格力1匹空调','空调'
select * from 物料表
end
if object_id('test..采购表') is null
begin
create table 采购表(编号 varchar(6),名称 varchar(40),单价 numeric(10,2),数量 int,小计 numeric(10,2),日期 datetime)
insert into 采购表
select 'A00001','46寸电视机',5000.00,10,5000*10,'2016-10-01' union all
select 'A00002','52寸电视机',5500.00,20,5500*20,'2016-10-02' union all
select 'A00003','60寸电视机',6500.00,10,6500*10,'2016-10-03' union all
select 'A00004','39寸电视机',3000.00,10,3000*10,'2016-10-04' union all
select 'A00005','16升洗衣机',1500.00,10,1500*10,'2016-10-04' union all
select 'A00007','美的1匹空调',2500.00,20,2500*20,'2016-10-05' union all
select 'A00008','格力1匹空调',2800.00,10,2800*10,'2016-10-05'
select * from 采购表
end
--1.2 建立1个修改物料表名称的存储过程，同时更新采购表名称（注意：建立存储过程的语句，要单独分开执行，即不能和上面建立测试环境的语句在一起）
create proc dbo.update_wl(@bh varchar(6),@mc varchar(40),@ut int output)
as
begin
declare @rs int
set @ut=1
begin transaction
update 物料表 set 名称=@mc where 编号=@bh
set @ut=@@rowcount
if @ut> 0 --@@rowcount为系统变量，影响行数，大于0表示更新成功，同步采购表
begin
if exists(select 编号 from 采购表 where 编号=@bh) --如果采购表存在改编号记录，同步
begin
update 采购表 set 名称=@mc where 编号=@bh
set @ut=@@rowcount --如果同步成功，必定返回大于0值
end
end
if @ut > 0
commit
else
rollback transactionend
--1.3 在SQL2000 中的调用方法
declare @bh varchar(6),@mc varchar(40),@ut int
set @bh='a00002'
set @mc='49寸电视机'
exec update_wl @bh,@mc,@ut output
select @ut
select * from 物料表
select * from 采购表
--只增加1个物料，采购没数据
--insert into 物料表
--select 'A00009','美的2匹空调','空调'
set @bh='a00009'
set @mc='美的2匹空调'
exec update_wl @bh,@mc,@ut output
select @ut
select * from 物料表
select * from 采购表
dbo.update_wl(@bh varchar(6),@mc varchar(40),@ut int output)
这个存储过程，是带参数返回值的，如果返回值大于0，表示更新成功。
相当执行了2条命令
update 物料表 set 名称=@mc where 编号=@bh
update 采购表 set 名称=@mc where 编号=@bh
但用存储过程，使用了事务，当2条语句都成功执行时，才都执行，要是第2条，没成功执行时，会回滚
**在VFP中调用
local bh,mc,ut
bh='A00002'
mc='49寸电视机'
ut=0
sqlexec(句柄,'exec update_wl ?bh,?mc,?@ut')
?ut &&查看返回值，0为没更新，大于0为更新
-- SQL2000中调用，'B00002'是物料表中没有的，此时，返回值 0
declare @bh varchar(6),@mc varchar(40),@ut int
set @bh='B00002'
set @mc='49寸电视机'
exec update_wl @bh,@mc,@ut output
select @ut
select * from 物料表
select * from 采购表
-- 1.4 存在过程的修改，在SQL2000中进行，把 create 改为 alter 就可以。
alter proc dbo.update_wl(@bh varchar(6),@mc varchar(40),@ut int output)
as
begin
declare @rs int --此行多余
set @ut=1
begin transaction
update 物料表 set 名称=@mc where 编号=@bh
set @ut=@@rowcount
if @ut> 0 --@@rowcount为系统变量，影响行数，大于0表示更新成功，同步采购表
begin
if exists(select 编号 from 采购表 where 编号=@bh) --如果采购表存在改编号记录，同步
begin
update 采购表 set 名称=@mc where 编号=@bh
set @ut=@@rowcount --如果同步成功，必定返回大于0值
end
end
if @ut > 0
commit
else
rollback transactionend
** 1.5 在VFP中，创建SQL2000的存储过程
TEXT TO lcSqlStr TEXTMERGE NOSHOW PRETEXT 4
create proc dbo.update_wl(@bh varchar(6),@mc varchar(40),@ut int output)
as
begin
declare @rs int --此行多余
set @ut=1
begin transaction
update 物料表 set 名称=@mc where 编号=@bh
set @ut=@@rowcount
if @ut> 0 --@@rowcount为系统变量，影响行数，大于0表示更新成功，同步采购表
begin
if exists(select 编号 from 采购表 where 编号=@bh) --如果采购表存在改编号记录，同步
begin
update 采购表 set 名称=@mc where 编号=@bh
set @ut=@@rowcount --如果同步成功，必定返回大于0值
end
end
if @ut > 0
commit
else
rollback transactionend
ENDTEXT
?SQLEXEC(句柄,lcSqlStr)
**就这么简单
LOCAL lcSql,lcServer,lcUid,lcPwd,lcPwd,lnHandle
lcServer = "atm8505"
lcUid = "sa"
lcPwd = "123456"
lcDbs = "test"
**把上面的参数，改为你自己的
lcSql=[driver=sql server;server=] + lcServer + [;uid=] + lcUid + [;pwd=] + lcPwd + [;database=] + lcDbs
lnHandle=sqlstringconnect(lcSql)
LOCAL bh,mc,ut
bh='A00002'
mc='49寸电视机'
ut=0
IF lnHandle > 0
?ut && 输出 0
SQLEXEC(lnHandle,'exec update_wl ?bh,?mc,?@ut')
?ut && 更新成功，输出大于1
ENDIF
*还有1种调用，用 call
IF lnHandle > 0
?ut && 输出 0
SQLEXEC(lnHandle,"{call update_wl(?bh,?mc,?@ut)}")
?ut && 更新成功，输出大于1
ENDIF
