SET NOCOUNT ON
GO
SET ARITHABORT OFF
GO
exec sp_name paramvalue
GO

CREATE PROCEDURE [dbo].[sp_name]
@paramvalue VARCHAR(30)
AS

BEGIN

BEGIN TRANSACTION

UPDATE TBL1
	SET COL1 = T2.COL3
FROM 
	TBL1 T1 INNER JOIN 
	TBL2 T2 ON T1.COL1 = T2.COL1

IF @@ERROR <> 0 
	GOTO ERR_COL1

UPDATE TBL1
	SET COL2 = T3.COL4
FROM 
	TBL1 T1 INNER JOIN 
	TBL3 T3 ON T1.COL1 = T3.COL1

IF @@ERROR <> 0 
	GOTO ERR_COL2

COMMIT TRANSACTION 
RETURN 0 -- How do I capture this return value..

ERR_COL1:
	SELECT @ERR_DESC = 'ERROR UPDATING COL1'
	GOTO ERROR_HANDLER

ERR_COL2:
	SELECT @ERR_DESC = 'ERROR UPDATING COL2'
	GOTO ERROR_HANDLER

ERROR_HANDLER:
	RAISERROR (@ERR_DESC,18,1)
	ROLLBACK TRANSACTION
	RETURN -1 -- How do I capture this return value..
	
END

quote:

---

Originally posted by tkizer

You will need to print it out that way it goes in the file:

declare @rc int
exec @rc = sp_name paramvalue

<SELECT or PRINT> 'Return value: ' + CONVERT(varchar(5), @rc)

I believe both SELECT and PRINT will work, but please do test it out.


Tara Kizer
http://weblogs.sqlteam.com/tarad/

---

SET NOCOUNT ON
GO
SET ARITHABORT OFF
GO
DECLARE @rc int
exec @rc = sp_name paramvalue
SELECT '(return status = '+CONVERT(VARCHAR,@rc)+')' 
GO

                                                
------------------------------------------------
(return status = 0)                             

SET NOCOUNT ON
GO
SET ARITHABORT OFF
GO
DECLARE @rc int
exec @rc = sp_name paramvalue
PRINT'(return status = '+CONVERT(VARCHAR,@rc)+')' 
GO

(return status = 0)