900字范文 > SQL server 存储过程的建立和调用

SQL server 存储过程的建立和调用

时间：2023-07-14 08:27:43

存储过程的建立和调用

--1.1准备测试需要的数据库:test,数据表:物料表,采购表

if not exists (select * from master.dbo.sysdatabases where name='test')

create database test

use test

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,'-10-01' union all

select 'A00002','52寸电视机',5500.00,20,5500*20,'-10-02' union all

select 'A00003','60寸电视机',6500.00,10,6500*10,'-10-03' union all

select 'A00004','39寸电视机',3000.00,10,3000*10,'-10-04' union all

select 'A00005','16升洗衣机',1500.00,10,1500*10,'-10-04' union all

select 'A00007','美的1匹空调',2500.00,20,2500*20,'-10-05' union all

select 'A00008','格力1匹空调',2800.00,10,2800*10,'-10-05'

select * from 采购表

end

--1.2 建立1个修改物料表名称的存储过程，同时更新采购表名称（注意：建立存储过程的语句，要单独分开执行，即不能和上面建立测试环境的语句在一起）

create proc dbo.update_wl(@bh varchar(6),@mc varchar(40),@ut int output)

begin

declare @rs int

set @ut=1

begin transaction

update 物料表 set 名称=@mc where 编号=@bhset @ut=@@rowcountif @ut> 0 --@@rowcount为系统变量，影响行数，大于0表示更新成功，同步采购表beginif exists(select 编号 from 采购表 where 编号=@bh) --如果采购表存在改编号记录，同步begin update 采购表 set 名称=@mc where 编号=@bhset @ut=@@rowcount --如果同步成功，必定返回大于0值endendif @ut > 0 commitelserollback transaction

end

--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)

begin

declare @rs int --此行多余

set @ut=1

begin transaction

end

** 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)

begin

declare @rs int --此行多余

set @ut=1

begin transactionupdate 物料表 set 名称=@mc where 编号=@bhset @ut=@@rowcountif @ut> 0 --@@rowcount为系统变量，影响行数，大于0表示更新成功，同步采购表beginif exists(select 编号 from 采购表 where 编号=@bh) --如果采购表存在改编号记录，同步begin update 采购表 set 名称=@mc where 编号=@bhset @ut=@@rowcount --如果同步成功，必定返回大于0值endendif @ut > 0 commitelserollback transaction

end

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)