CLR Store Procedures Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CLR Store Procedures Problem

Author

Topic

new2this
Starting Member

16 Posts

Posted - 2008-05-09 : 19:43:06

Here's the code I have but in the try/catch area it always does the catch portion. what could i be doing wrong?

Public Shared Sub GetLineItemCountDateRange(ByRef lineItemCount As SqlInt32, ByVal startDate As SqlDateTime, ByVal endDate As SqlDateTime)

' open the connection object for the context
Dim connection As New SqlConnection("Context connection = true")
connection.Open()

' the string that returns a count of the line items
' in the InvoiceLineItem table for invoice between
' the start and end dates.
Dim sql As String = _
"select count(*)" & _
"from InvoiceLineItems join Invoices" & _
"on InvoiceLineItems.InvoiceID = Invoices.InvoiceID" & _
"where (Invoices.InvoiceDate > @startDate) and " & _
"(Invoices.InvoiceDate < @endDate)"

' create the comman object
Dim command As New SqlCommand(sql, connection)

' add new parameters to the Parameters collection
Dim param As New SqlParameter("@startDate", SqlDbType.DateTime)
param.Value = startDate
command.Parameters.Add(param)

param = New SqlParameter("@endDate", SqlDbType.DateTime)
param.Value = endDate
command.Parameters.Add(param)

' assign a value to the output parameter
Try
dim scalarValue As Integer = Convert.ToInt32(command.ExecuteScalar)
lineItemCount = CType(scalarValue, SqlInt32)
Catch ex As Exception
lineItemCount = 0
End Try

' close the connection object
connection.Close()
End Sub
End Class

Here's my script:

declare @lineItemCountOut int
exec GetLineItemCountDateRange
@lineItemCount =@lineItemCountOut output,
@startDate = '2006-01-01',
@endDate = '2006-05-30'
print 'Line item count: ' + convert(varchar, @lineItemCountOut, 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources