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)