SQL server 系统储存过程在VB中的应用

以锁定应用程序资源(sp_getapplock / sp_releaseapplock)为例:

sp_getapplock没有返回参数(output类型的),只是在sp中用return来返回值

加锁:

dim cmd as adodb.command
set cmd = new adodb.command
cmd.activeconnection = cnn
cnn.begintrans
cnn.execute "use master"
cmd.commandtype = adcmdstoredproc
cmd.commandtext = "sp_getapplock"
系统识别格式为:'"{ ? = call sp_getapplock(?,?,?,?) }"
cmd.prepared = true
cmd.parameters(1) = "lock name"    '资源锁的名称
cmd.parameters(2) = "exclusive"
cmd.parameters(3) = "transaction"
cmd.parameters(4) = 0                        '用于接收sp 中return关键字返回的值
cmd.execute
msgbox cmd.parameters(0)               'display return value

解锁:

dim cmd as adodb.command
set cmd = new adodb.command
cmd.activeconnection = cnn
cmd.commandtype = adcmdstoredproc
cmd.commandtext = "sp_releaseapplock"
cmd.parameters(1) = "lock name"           '被锁资源名称
cmd.parameters(2) = "transaction"
cmd.execute
msgbox cmd.parameters(0)