SQL server 存儲過程的建立和調用

SQL server 存儲過程的建立和調用

存儲過程的建立和調用

--1.1準備測試需要的資料庫:test,資料表:物料表,采購表

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

create database test

go

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

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 采購表

--1.2 建立1個修改物料表名稱的存儲過程，同時更新采購表名稱（注意：建立存儲過程的語句，要單獨分開執行，即不能和上面建立測試環境的語句在一起）

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

as

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 transaction           

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

--隻增加1個物料，采購沒資料

--insert into 物料表

--select 'A00009','美的2匹空調','空調'

set @bh='a00009'

set @mc='美的2匹空調'

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

set @bh='B00002'

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

** 1.5 在VFP中，建立SQL2000的存儲過程

TEXT TO lcSqlStr TEXTMERGE NOSHOW PRETEXT 4

as

begin

declare @rs int --此行多餘

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 transaction           

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

IF lnHandle > 0

?ut && 輸出 0

SQLEXEC(lnHandle,'exec update_wl ?bh,?mc,?@ut')

?ut && 更新成功，輸出大于1

ENDIF

*還有1種調用，用 call

SQLEXEC(lnHandle,"{call update_wl(?bh,?mc,?@ut)}")

原文位址

https://www.cnblogs.com/liu224/p/10736111.html