Member Avatar for Mike Bishop

i am trying to write a SP that will return a value in vb.net

here is my SP

CREATE PROCEDURE ClaimTotal @Ctotal BIGINT output
AS
select SUM(total) from claim
where @ctotal = total

I want to SUM my total coloum from my table claim, and store this in a
varible called @ctotal so then i can pull this into vb.net

this is all new to me, as i do normal select queries but i have read that stored procedures are the way to go.

please please help a newbie

also how would i then pull the @ctotal into vb.net?

  • 2 Contributors
  • 1 Reply
  • 186 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by prashantchalise

Dani AI

Generated

Short summary and corrections: wants the sum of the total column and to retrieve it from VB.NET. The original procedure’s WHERE @ctotal = total is incorrect (that compares, it does not assign). was right to suggest computing the aggregate inside the stored procedure, but their VB sample contains an indexing bug (reading p(9) when only p(0) was created) and a type mismatch: SQL BIGINT maps to a 64-bit integer in .NET (Long / Int64), not Integer.

A simple, robust pattern is to have the procedure return the aggregate as a single-column result (this is easy to read from ADO.NET). Example SQL that avoids NULL results:

CREATE PROCEDURE ClaimTotal
AS
BEGIN
  SET NOCOUNT ON;
  SELECT ISNULL(SUM([total]), 0) AS TotalSum
  FROM claim;
END

Calling that from VB.NET with ADO.NET is straightforward using ExecuteScalar and converting safely to Long:

Dim result As Object
Using cn As New SqlConnection(connectionString)
  Using cmd As New SqlCommand("ClaimTotal", cn)
    cmd.CommandType = CommandType.StoredProcedure
    cn.Open()
    result = cmd.ExecuteScalar()
  End Using
End Using

Dim cTotal As Long = If(result Is Nothing OrElse IsDBNull(result), 0L, Convert.ToInt64(result))

Notes and troubleshooting: if an OUTPUT parameter is preferred, ensure the parameter is added to the command with Direction = Output and read it from cmd.Parameters("@Ctotal").Value (do not mix array indexes incorrectly). Always use Long/Int64 for BIGINT, guard against NULL with ISNULL/COALESCE, and include SET NOCOUNT ON in procs to prevent extra rowcount result sets. Wrap connections/commands in Using blocks to avoid leaks. For large tables, consider an indexed approach or filtered aggregation for performance.

Member Avatar for prashantchalise

Hi,

Initially you need to bind the total Sum to the @Ctotal (as Ctotal is OutPut parameter) in your SP with name "ClaimTotal" in following manner,

SELECT @Ctotal = SUM(total) FROM claim

Afterwards, You need to create proper code block to find value in @Ctotal.

Dim cTotal As Integer
Dim p(1) As SqlParameter
p(0) = New SqlParameter("@Ctotal", SqlDbType.BigInt)
p(0).Direction = ParameterDirection.Output
ExecuteNonQuery(CommandType.StoredProcedure, "ClaimTotal", p)
cTotal = CInt(p(9).Value)

Please Note that, ExecuteNonQuery is a method described in Microsoft Application Block for .NET and can be used as to perform the most common data access tasks against a Microsoft SQL Server 200X database. You can download it from


Thanks,

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.