VB调用存储过程的例子 前言

(说明:以下代码摘自微软的MSDN,经过测试没问题。)



VB调用存储过程的方法很多，如利用ADO对象的Recordset.Open方法，ADO对象的Connection.Excute方法等，都可以获得记录集信息。本主题讨论的是使用Parameter对象调用存储过程，而且可以获得许多意外的信息。


首先需要在Sql Server中建立一个存储过程。请确定已安装了Sql Server 2000的任何版本，且含有Pubs数据库。

打开“查询分析器”，启动你本地的SqlServer，然后将以下的Sql语句Copy到所打开的查询编辑框中。按下“F5”键。OK，存储过程ADOTestRPE便生成了。



Use Pubs

GO

if exists (select * from sysobjects where id =

object_id('dbo.ADOTestRPE') and sysstat & 0xf = 4)

drop procedure dbo.ADOTestRPE

GO



create procedure ADOTestRPE

(

@SetRtn INT=0 OUTPUT,

@R1Num INT=1,

@P1Num INT=1,

@E1Num INT=1,

@R2Num INT=2,

@P2Num INT=2,

@E2Num INT=2

)

AS

DECLARE @iLoop INT

DECLARE @PrintText VARCHAR(255)

DECLARE @iErrNum INT



/* Check for no Resultsets - needed to get the RETURN value back */

IF @R1Num + @R2Num = 0 SELECT NULL



/* Resultset 1 ******************************* */



IF @R1Num > 0

BEGIN

SET ROWCOUNT @R1Num

SELECT 'Resultset 1' RsNum, Title

FROM Pubs..Titles

SET ROWCOUNT 0

End



/* Must raise a default error context in which to return the PRINT */

/* statement */

/* (if none present) since PRINT statements are a severity level of */

/*0. */

IF (@P1Num > 0) AND (@E1Num = 0) RAISERROR ("RAISERROR.PError1",

11, 2)



IF @P1Num > 0

BEGIN

SELECT @iLoop = 0

WHILE @iLoop < @P1Num

BEGIN

SELECT @iLoop = @iLoop + 1

SELECT @PrintText = 'PRINT.Resultset.1: Line ' +

CONVERT(char(2), @iLoop)

PRINT @PrintText

End

End



IF @E1Num > 0

BEGIN

SELECT @iLoop = 0

WHILE @iLoop < @E1Num

BEGIN

SELECT @iLoop = @iLoop + 1

SELECT @iErrNum = @iLoop + 201000

RAISERROR ("RAISERROR.Resultset.1", 11, 2)

End

End



/* Resultset 2 ******************************* */



IF @R2Num > 0

BEGIN

SET ROWCOUNT @R2Num

SELECT 'Resultset 2' RsNum, Title

FROM Pubs..Titles

SET ROWCOUNT 0

End



/* Must raise a default error context in which to return the PRINT */

/* statement */

/* (if none present) since PRINT statements are a severity level of */

/* 0. */

IF (@P2Num > 0) AND (@E2Num = 0) RAISERROR ("RAISERROR.PError2",

11, 2)



IF @P2Num > 0

BEGIN

SELECT @iLoop = 0

WHILE @iLoop < @P2Num

BEGIN

SELECT @iLoop = @iLoop + 1

SELECT @PrintText = 'PRINT.Resultset.2: Line ' +

CONVERT(char(2), @iLoop)

PRINT @PrintText

End

End



IF @E2Num > 0

BEGIN

SELECT @iLoop = 0

WHILE @iLoop < @E2Num

BEGIN

SELECT @iLoop = @iLoop + 1