Listing 2: CLR-Based Solution Using Visual Basic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub SalesRunningSum()
Using conn As New SqlConnection("context connection=true")
Dim comm As New SqlCommand
comm.Connection = conn
comm.CommandText = "" & _
"SELECT empid, dt, qty " & _
"FROM dbo.Sales " & _
"ORDER BY empid, dt;"
Dim columns() As SqlMetaData = New SqlMetaData(3) {}
columns(0) = New SqlMetaData("empid", SqlDbType.Int)
columns(1) = New SqlMetaData("dt", SqlDbType.DateTime)
columns(2) = New SqlMetaData("qty", SqlDbType.Int)
columns(3) = New SqlMetaData("sumqty", SqlDbType.BigInt)
Dim record As New SqlDataRecord(columns)
SqlContext.Pipe.SendResultsStart(record)
conn.Open()
Dim reader As SqlDataReader = comm.ExecuteReader
Dim prvempid As SqlInt32 = 0
Dim sumqty As SqlInt64 = 0
While (reader.Read())
Dim empid As SqlInt32 = reader.GetSqlInt32(0)
Dim qty As SqlInt32 = reader.GetSqlInt32(2)
If (empid = prvempid) Then
sumqty = sumqty + qty
Else
sumqty = qty
End If
prvempid = empid
record.SetSqlInt32(0, reader.GetSqlInt32(0))
record.SetSqlDateTime(1, reader.GetSqlDateTime(1))
record.SetSqlInt32(2, qty)
record.SetSqlInt64(3, sumqty)
SqlContext.Pipe.SendResultsRow(record)
End While
SqlContext.Pipe.SendResultsEnd()
End Using
End Sub
End Class