STORED PROC: RETURN value not being returned when <> 0

[whool]

I am calling the following sp using ADO from VB:

BEGIN
	INSERT INTO IMPListAssocRecipientTbl values(@ListID,@ReceipientID,@ReceipientLastName,@ListName)
	IF (@@error <> 0) OR (@@rowcount <> 1)
	  BEGIN
		
		RETURN 999
	  END
END [\code]


If the error/rowcount condition is not met I get a return value of 0 in my ADORetrnVal-parmarter, Great, no probs. 
However if the condition is met (typically with a Cannot Insert Dupe Key error) a msg is sent to client, but no value is being returned to the ADOparameter. Yet when I step through the code I see the cursor step into the RETURN Line.

Does anybody know where my 999 might be?

Yael

 

[bperry]

I'd like for someone to clear this up for me, too. I have recently come to the conclusion, from behavior I have seen, that there are certain fatal errors that will bomb the SP such that nothing gets returned.

Someone please tell me I am wrong about this!

 

[tlbroadbent]

The snippet of code that you posted looks OK. Do you have

SET NOCOUNT ON

at the start of the SP? If not add the command and test to see if that helps. If not, I believe the error is in your VB code. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[whool]

Thanks Terry,

Have tried your recommendation to no avail. Books Online says that SET NOCOUNT deals only with rows affected.The the error I'm trying to trap is a key violation which does not affect rows so the setting should have no bearing
Have checked and rechecked VB code - I'm sure its OK.

I find RETURN works fine for a non error condition eg,

IF @Varible = 0
BEGIN
RETURN 999
END

, but with the error mentioned the sp just aborts and returns nothing, just like bPerry said.

Cheers,
Yael

 

[tlbroadbent]

I've struggled with several questions tonight because I haven't read carefully. Should probably go to bed.

You can check for the existence of a matching row before inserting. Thus you can avoid the duplicate error.

IF Not Exists
(Select * From IMPListAssocRecipientTbl
Where ListId=@ListID
And ReceipientID=@ReceipientID
And ReceipientLastName=@ReceipientLastName
And ListName=@ListName)
BEGIN

INSERT INTO IMPListAssocRecipientTbl
VALUES (@ListID, @ReceipientID,
@ReceipientLastName, @ListName)

IF @@rowcount <> 1
BEGIN
RETURN 999 -- Insert failed
END
END
ELSE
RETURN 888 -- record already exists

I still think the SP is working correctly. That error is non-fatal. The stored procedure will continue to the next step.

The SP returns multiple result sets to the client. The error message will be in one result set and the return value in another. Your VB code must handle all of the results sets. Check the following link for more info about handling multiple results VB.

http://support.microsoft.com/support/kb/articles/Q245/1/79.ASP

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[cmmrfrds]

&quot;If the error/rowcount condition is not met I get a return value of 0 in my ADORetrnVal-parmarter, Great, no probs. &quot;

The default in return will be 0, so I don't know if the above statement tells you anything about returing a 0 from your stored procedure.

When using a server side cursor in ADO, the case by default, then the return values cannot be checked until the ADO recordset is closed. Also, you may be getting multiple recordsets returned from the SP.

Some things to help debug.
ADO has an error collection, so you could iterate through the collection to see if more descriptive information is available on an error.

Try closing the recordset and then accessing the return value.
rs.close
print &quot;return = &quot; cmd(0) 'or however you are identifying the return value
set rs = nothing

If you want to interate and see if multiple recordset are being returned then setup some kind of loop. Multiple recordset are returned under different conditions, such as, there are a couple of selects in the SP, or the SP is sending back informational messages like on an insert. The informational messages can be shut off with &quot;set nocount on&quot;.

While not(rs.state = adStateClosed)
set rs = rs.NextRecordSet
print &quot;something &quot; rs.state
Wend

 

[whool]

Thanks guys but I've found the answer:

I was using the wrong Data provider; OLDEB for ODBC instead of OLDDB for SQLServer. My understanding of APIs is very poor but I think what was happening is the ODBC (manager?) was treating the dupe key error as fatal and so not passing back return values. Would like to understand more about why this works (any good references Terry?) but may just have to take it for granted for now. Hope this works for you bperry.

Cheers,
Yael