使用存储过程返回Max(Returning Max with a stored procedure)

我已经阅读并重试了以下许多方面重建以下内容，但为了清楚说明，我将展示我的最后一次尝试。

目标 - 获取列“UniqueID”的最大值

列字段'uniqueID'设置为bigint

我假设错误是在addwithvalue的行中，因为我得到“int”作为返回值

如果我SELECT MAX(UniqueID) FROM tblResults in SQL运行查询SELECT MAX(UniqueID) FROM tblResults in SQL它可以工作

码

Dim connection5 As SqlConnection Dim command5 As New SqlCommand Dim ds5 As New DataSet Dim ConnectionString5 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").ToString() connection5 = New SqlConnection(ConnectionString5) connection5.Open() command5.Connection = connection5 command5.Parameters.Clear() command5.CommandText = "spUniqueUserID" command5.Parameters.AddWithValue("@UniqueID", SqlDbType.BigInt) command5.Parameters("@UniqueID").Direction = ParameterDirection.Output command5.CommandType = CommandType.StoredProcedure command5.ExecuteNonQuery() Session.Item("UniqueID") = command5.Parameters("@UniqueID").Value connection5.Close() Dim vShow As String vShow = "" vShow = Session.Item("UniqueID").ToString

SP

USE [DB] GO /****** Object: StoredProcedure [dbo].[spUniqueUserID] Script Date: 09/10/2013 08:51:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spUniqueUserID] @UniqueID bigint OUTPUT AS BEGIN select @UniqueID = (SELECT MAX(UniqueID) FROM tblResults ) Return @UniqueID END

I have read and retried rebuilding the below in many ways but to keep it clear I will show my last attempt.

Aim - To get the Max value of column "UniqueID"

The column field 'uniqueID' is set as a bigint

I assume the error is in the line with addwithvalue in as I get "int" as the return value

If I run the query SELECT MAX(UniqueID) FROM tblResults in SQL it works

Code

Dim connection5 As SqlConnection Dim command5 As New SqlCommand Dim ds5 As New DataSet Dim ConnectionString5 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").ToString() connection5 = New SqlConnection(ConnectionString5) connection5.Open() command5.Connection = connection5 command5.Parameters.Clear() command5.CommandText = "spUniqueUserID" command5.Parameters.AddWithValue("@UniqueID", SqlDbType.BigInt) command5.Parameters("@UniqueID").Direction = ParameterDirection.Output command5.CommandType = CommandType.StoredProcedure command5.ExecuteNonQuery() Session.Item("UniqueID") = command5.Parameters("@UniqueID").Value connection5.Close() Dim vShow As String vShow = "" vShow = Session.Item("UniqueID").ToString

SP

USE [DB] GO /****** Object: StoredProcedure [dbo].[spUniqueUserID] Script Date: 09/10/2013 08:51:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spUniqueUserID] @UniqueID bigint OUTPUT AS BEGIN select @UniqueID = (SELECT MAX(UniqueID) FROM tblResults ) Return @UniqueID END

最满意答案

我个人不会搞乱输出参数。简单地使用

ALTER PROCEDURE [dbo].[spUniqueUserID] AS BEGIN SELECT MAX(UniqueID) FROM tblResults END

在你的过程中，和

Dim sqlConnection1 As New SqlConnection("Your Connection String") Dim cmd As New SqlCommand Dim returnValue As Object cmd.CommandText = "spUniqueUserID" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = sqlConnection1 sqlConnection1.Open() returnValue = cmd.ExecuteScalar() sqlConnection1.Close()

在你的代码中。（最好使用连接和命令的语句，为简洁起见，此处缺少）

I personally wouldnt mess around with the output parameter. Simply use

ALTER PROCEDURE [dbo].[spUniqueUserID] AS BEGIN SELECT MAX(UniqueID) FROM tblResults END

in your proc, and

Dim sqlConnection1 As New SqlConnection("Your Connection String") Dim cmd As New SqlCommand Dim returnValue As Object cmd.CommandText = "spUniqueUserID" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = sqlConnection1 sqlConnection1.Open() returnValue = cmd.ExecuteScalar() sqlConnection1.Close()

In your code. (best with using statements for your connection and command, here missing, for brevity)

更多推荐