如何使用VB作为调用代码从存储过程中检索返回值 [英] How do I retrieve the return value from a stored procedure with VB as the calling code

问题描述

使用VB 2012和SQL Server 2012.书面和测试的存储过程根据过程的成功返回值。存储过程工作得很好，但我很难正确编写代码来检索返回值，所以我可以处理它。



什么不断返回我的变量是一个-1。无论NOCOUNT设置为ON还是OFF，都会发生这种情况。



我尝试过的事情：



SQL Server存储过程：



USE [BEClientData]

GO

/ ******对象：StoredProcedure [dbo]。[sprocAuthorizeLogin]脚本日期：9/20/2016 9:35:48 AM ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

- ============= ================================

- 作者：Stan Bibbs

- 创建日期：2016年9月18日

- 描述：检查登录提交

- ============= ================================

ALTER PROCEDURE [dbo]。[sprocAuthorizeLogin]

- 在这里添加存储过程的参数

@Username nvarchar（20）= 0，

@Password nvarchar（20）= 0



AS



BEGIN

- 添加SET NOCOUNT ON以防止来自
- 干扰SELECT语句。

SET NOCOUNT ON;





- 在此处插入程序声明

如果EXISTS（选择用户名，密码

来自dbo.SignInTable

WHERE用户名= @Username和密码= @Password）



返回2



ELSE



返回1



结束



VB函数调用程序：



公共函数ValidateLogin（ByVal strUsername As String，ByVal strPassword As String）As Integer



Dim intExists As Integer



Dim cn As New SqlConnection（My.Settings.BEClientDataConnectionString）



Dim procName As String =sprocAuthorizeLogin

Dim cmd As New SqlCommand（procName，cn）

with cmd

.CommandType = CommandType.StoredProc edure

.CommandText = procName

.Parameters.Add（@ Username，SqlDbType.NVarChar，20）.Value = strUsername

。 Parameters.Add（@ Password，SqlDbType.NVarChar，20）.Value = strPassword

结束



cn.Open（）



intExists = cmd.ExecuteNonQuery（）



返回intExists

Using VB 2012 and SQL Server 2012. Written and tested stored procedure to return a value depending upon success of procedure. Stored procedure works perfectly, but I am having difficulty writing the code correctly to retrieve the return value so I can process it.

What keeps getting returned to my variable is a -1. This happens regardless of whether NOCOUNT is set to ON or OFF.

What I have tried:

SQL Server stored procedure:

USE [BEClientData]
GO
/****** Object: StoredProcedure [dbo].[sprocAuthorizeLogin] Script Date: 9/20/2016 9:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Stan Bibbs
-- Create date: 18 Sep 2016
-- Description: Check login submission
-- =============================================
ALTER PROCEDURE [dbo].[sprocAuthorizeLogin]
-- Add the parameters for the stored procedure here
@Username nvarchar(20) = 0,
@Password nvarchar(20) = 0

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


-- Insert statements for procedure here
IF EXISTS(SELECT Username, Password
FROM dbo.SignInTable
WHERE Username = @Username AND Password = @Password)

RETURN 2

ELSE

RETURN 1

END

VB function which calls procedure:

Public Function ValidateLogin(ByVal strUsername As String, ByVal strPassword As String) As Integer

Dim intExists As Integer

Dim cn As New SqlConnection(My.Settings.BEClientDataConnectionString)

Dim procName As String = "sprocAuthorizeLogin"
Dim cmd As New SqlCommand(procName, cn)
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = procName
.Parameters.Add("@Username", SqlDbType.NVarChar, 20).Value = strUsername
.Parameters.Add("@Password", SqlDbType.NVarChar, 20).Value = strPassword
End With

cn.Open()

intExists = cmd.ExecuteNonQuery()

Return intExists

推荐答案

试试这个

try this

ALTER PROCEDURE [dbo].[sprocAuthorizeLogin] 
@Username nvarchar(20) = 0, 
@Password nvarchar(20) = 0
AS
BEGIN
IF EXISTS(SELECT Username, Password FROM dbo.SignInTable WHERE Username = @Username AND Password = @Password)
select 2 
ELSE 
select 1
END

intExists =  Convert.ToInt32(cmd.ExecuteScalar())

参考 ExecuteScalar与ExecuteNonQuery [ ^ ]

您正在使用ExecuteNonQuery进行查询。 ExecuteNonQuery是为INSERTS / DELETES / UPDATES设计的，所以返回的值是受影响的行数。

You're using ExecuteNonQuery for a query. ExecuteNonQuery is designed for INSERTS/DELETES/UPDATES so the value returned is the count of rows affected.

据我所知，没有直接的方法来获取SP的返回值在SSMS之外 - ExecuteNonQuery不会这样做 - 它返回受影响的行数，而不是RETURN值。并且ExecuteScalar也不会，除非结果是SELECTed。

如果你可以修改SP来选择值而不是/以及返回它，那么你就可以了。如果不是它们，你需要创建第二个SP才能做到这一点：

As far as I know, there is no direct way to get the return value of an SP outside SSMS - ExecuteNonQuery won't do it - it returns the number of rows affected, not the RETURN value. And ExecuteScalar won't either, unless the result its SELECTed.
If you can modify your SP to SELECT the value instead of / as well as RETURNing it, then your are ok. If not them you need to create a second SP to do just that:

CREATE PROCEDURE spGetTheRETURNValue
AS
BEGIN
   RETURN 666
END
CREATE PROCEDURE spGetTheActualValue
AS
BEGIN
   DECLARE @ret INT
   EXEC @ret = spGetTheReturnValue
   SELECT @ret
END

然后你就可以了使用ExecuteScalar创建新过程并以该方式检索值。

You can then call the new procedure using ExecuteScalar and retrieve the value that way.

这篇关于如何使用VB作为调用代码从存储过程中检索返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！