Hi everyone, I have some CF code that calls on a SQL Server 2000 stored
procedure. The procedure has logic where it tries to

insert into a table and if the insert fails, then the procedure queries
another table and returns a resultset.

I'm able to ignore the Integrity Constraint Violation error (#23000)
which is ok. The problem is that I can't read the data

returned after the error. Here is the code...

<CFSTOREDPROC procedure="dbo.checkComments"
datasource="#getCommentsDSNForUserID(MyUserID)#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER"
dbvarname="@UserID" value="#myuserID#">
<cfprocresult name="commentPosts">
</CFSTOREDPROC>

The CF code calls this procedure just fine...

CREATE PROCEDURE dbo.checkComments
@UserID int
AS

INSERT INTO dbo.AggregateData (UserID, HasNewMail,
CommentPostsToday)
Values(@UserID, 0, 0)

IF @@ERROR = 0
BEGIN
SELECT 0
END
ELSE
BEGIN
SELECT CommentPostsToday FROM dbo.AggregateData WHERE UserID =
@UserID
END

When I call dbo.checkComments from SQL Server Query Analyzer for a
UserID that exists, the error is returned, but so is the

value for CommentPostsToday for the user. When I call the procedure
from CF though, this is what I get...

ODBC Error Code = 23000 (Integrity constraint violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY
KEY constraint 'PK_AggregateData'.
Cannot insert duplicate key in object 'AggregateData'.

SQL = "dbo.checkComments"

Data Source = "MAIL2"

The error occurred while processing an element with a general
identifier of (CFSTOREDPROC)

I am able to trap for this error and ignore it though...

<cfcatch type="Any">
</cfcatch>
</cftry>
Printing out counter:
<cfoutput>#commentPosts.CommentPostsToday#</cfoutput>

****************************
My main question is this, once I ignore the error, how do I read the
value for CommentPostsToday that the procedure returns?

I've tested the procedure with SQL Server Query Analyzer and I know
that the procedure returns the value. Here is the code I

use to read the value, but it isn't getting it. If an error occurs in a
sql stored procedure, is it impossible to get a

recordset from the procedure even if the procedure handles the error? I
tried using an output parameter instead of a

resultset and that failed to return the value too.

I'm still pretty new to Coldfusion so I'd greatly appreciate any help
anyone can provide. Thank you.

Gus