Hi,

I have a problem with return parameters. This one returns zero even when it shouldn't.



My sproc is a bunch of data validation and insert statements and if any fail I rollback the transaction and return a negative

value that I can refer back the step that failed. As a result of my return value problems, I'm getting sprocs rolling back but not

'telling' my asp script.



Am I doing it wrong?



Thanks,

walter



---------------

asp code:

---------------

dim objCommAddBatch

set objCommAddBatch = server.CreateObject("ADODB.Command")

objCommAddBatch.ActiveConnection = strConnect

objCommAddBatch.CommandText = "isp_tempBirth"

objCommAddBatch.CommandType = adCmdStoredProc

set objParamReturn	= objCommAddBatch.CreateParameter("return",adInteger,adParamReturnValue)

set objParamCountryCode	= objCommAddBatch.CreateParameter("countryCode",adVarChar,adParamInput,5)

set objParamHerdNo	= objCommAddBatch.CreateParameter("herdNo",adVarChar,adParamInput,10)

set objParamAnimalNo	= objCommAddBatch.CreateParameter("animalNo",adVarChar,adParamInput,10)

[etc etc]

objCommAddBatch.Parameters.Append objParamReturn

objCommAddBatch.Parameters.Append objParamCountryCode

objCommAddBatch.Parameters.Append objParamHerdNo

objCommAddBatch.Parameters.Append objParamAnimalNo

[etc, etc]

objCommAddBatch.Parameters("countryCode")	= ckCountryCode

objCommAddBatch.Parameters("herdNo")	= ckHerdNo

objCommAddBatch.Parameters("animalNo")	= ckAnimalNo

[etc,etc]

objCommAddBatch.Execute adExecuteNoRecords



dim intReturn

intReturn = objCommAddBatch.Parameters("return")



set objParamReturn	= nothing

set objParamCountryCode	= nothing

set objParamHerdNo	= nothing

set objParamAnimalNo	= nothing

set objCommAddBatch	= nothing



-----------

SQL sproc:

-----------

CREATE PROCEDURE isp_tempBirth

	@countryCode		varchar(5),

	@herdNo		varchar(10),

	@animalNo		varchar(10),

[etc, etc]

AS

DECLARE @result int

BEGIN TRANSACTION tempBirth

	/* check the tag is valid/*

 	EXEC @result = csp_tagCheck @countryCode, @herdNo, @animalNo, 0

 	IF @result <> 0

 	BEGIN

		/* if not, reset @result, rollback the transaction and return -8

		SELECT @result = 0

  		ROLLBACK TRANSACTION writeBatch

  		RETURN -8

 	END





	SELECT

		id

	FROM

		tblTempBirth

	WHERE

		countryCode = @countryCode

	AND

		herdNo = @herdNo

	AND

		animalNo = @animalNo



	IF @@ROWCOUNT > 0

	BEGIN

		/* if rowcount > 0 then its already been inserted to rollback and return -1

		ROLLBACK TRANSACTION tempBirth

		RETURN -1

	END



	/* do the insertion

	INSERT INTO

		tblTempBirth

	(

		countryCode,

		herdNo,

		animalNo,

		etc

	)

	VALUES





		@countryCode,

		@herdNo,

		@animalNo,

		etc

	)

	IF @@ERROR <> 0

	BEGIN

		/* if the insertion fails, return -11 */

 		ROLLBACK TRANSACTION tempBirth

 		RETURN -11

	END



/* if we get this far, then it worked so commit the transaction */

COMMIT TRANSACTION tempBirth

--------------------------------------

I don't have a working machine with SQL server close to try it out, but 

have you tried using ELSE in combination with a temp error code?

I also added some comments here and there



Something like this might do the trick:





Declare @myErrorCode int

Set @myErrorCode = 0



IF @result <> 0

BEGIN

         /* if not, reset @result, rollback the transaction and return -8 

/* -- there was a missing comment closing tag here. Copy and paste problem??

         SELECT @result = 0

         ROLLBACK TRANSACTION writeBatch '-- this is not the name of the 

transaction you started. You started tempBirth

         Set @myErrorCode = -8

END

ELSE

BEGIN

         -- do something else here

         Set @myErrorCode = 0 -- can skip this. Has already been 

initialized to 0

END



Return @MyErrorCode





Imar





At 03:20 PM 1/23/2001 +0000, you wrote:

>Hi,

>I have a problem with return parameters. This one returns zero even when 

>it shouldn't.

>

>My sproc is a bunch of data validation and insert statements and if any 

>fail I rollback the transaction and return a negative

>value that I can refer back the step that failed. As a result of my return 

>value problems, I'm getting sprocs rolling back but not

>'telling' my asp script.

>

>Am I doing it wrong?

>

>Thanks,

>walter