sql - 使用存储过程返回 Max

Question

我已阅读并重试以多种方式重建以下内容，但为了保持清晰，我将展示我的最后一次尝试。

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

列字段“uniqueID”设置为 bigint

我认为错误出现在 addwithvalue 行中，因为我得到“int”作为返回值

如果我运行查询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

Answer 1

我个人不会乱搞输出参数。只需使用

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()

在你的代码中。 (最好使用用于连接和命令的 using 语句，为简洁起见，此处缺失)