Raising Errors through nested SPS

[hwkranger]

My brain isn't working this morning. I'm trying to figure out why when I'm catching errors I'm not able to see the actual SQL error through nested sps.

ALTER PROCEDURE [dbo].[uspSitePermission_Insert]
           (@UserID as int
           ,@SiteID as int
           ,@HistoryID as int
           ,@IsDeleted as bit
           ,@CreatedBy as int
           ,@CreatedOn as datetime
           ,@ContractorID as int

		   ,@SitePermissionID as int  = null OUTPUT
		   ,@Error_Number as int  = null OUTPUT
		   ,@Error_Message as varchar(500) = null  OUTPUT
			)

AS
BEGIN TRANSACTION

-- CHECK FOR NULL CONSTRAINTS

IF @IsDeleted is null
BEGIN
   SET @IsDeleted = 0
END
IF @CreatedOn is null
BEGIN
	SET @CreatedOn = getdate()
END


-- END CHECKING FOR CONSTRAINTS

	BEGIN TRY
INSERT INTO [dbo].[Site_Permission]
           ([STP_UserID]
           ,[STP_SiteID]
           ,[STP_HistoryID]
           ,[STP_IsDeleted]
           ,[STP_CreatedBy]
           ,[STP_CreatedOn]
           ,[STP_ContractorID])
     VALUES
           (@UserID 
           ,@SiteID 
           ,@HistoryID 
           ,@IsDeleted 
           ,@CreatedBy 
           ,@CreatedOn 
           ,@ContractorID )

			SET @SitePermissionID = SCOPE_IDENTITY()

	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		SELECT 
			 @Error_Number = ERROR_NUMBER()
			,@Error_Message = ERROR_MESSAGE()
		[b]SELECT @Error_Number, @Error_Message[/b]
		RAISERROR ('Error Inserting.'  , 16  , 1)
		
	END CATCH

IF @@TRANCOUNT > 0
	COMMIT TRANSACTION

The bold line does return the correct error message, those error message should be bubbled up to the next sp..

ALTER PROCEDURE [dbo].[uspSitePermission_Add]
           (@UserID as int = null
            ,@Users as nvarchar(500) = null
			,@SiteID as int
           ,@CreatedBy as int
           ,@ContractorID as int

		   ,@SitePermissionID as int  = null OUTPUT
		   ,@Error_Number as int  = null OUTPUT
		   ,@Error_Message as varchar(500) = null  OUTPUT
			)

AS
BEGIN TRANSACTION
BEGIN TRY

		IF @Users is null -- ADD SINGLE USER
		BEGIN
			EXEC uspSitePermission_Insert
		           
				   @UserID = @UserID
				   ,@SiteID = @SiteID
				   ,@HistoryID = null
				   ,@IsDeleted = 0
				   ,@CreatedBy = @CreatedBy
				   ,@CreatedOn = null
				   ,@ContractorID = @ContractorID
				   ,@SitePermissionID = @SitePermissionID 
				   ,@Error_Number = @Error_Number OUTPUT
				   ,@Error_Message = @Error_Message OUTPUT
		END	
		IF @Users is not null -- ADD 1 or MANY USERS
		BEGIN


			DECLARE @Num		int
			DECLARE @Pos		int
			DECLARE @NextPos	int

			SET @Num = 0
			SET @Pos = 1
			WHILE(@Pos <= LEN(@Users))
			BEGIN
				SELECT @NextPos = CHARINDEX(N',', @Users,  @Pos)
				IF (@NextPos = 0 OR @NextPos IS NULL)
					SELECT @NextPos = LEN(@Users) + 1
				SELECT @UserID = CONVERT(int, RTRIM(LTRIM(SUBSTRING(@Users, @Pos, @NextPos - @Pos))))

						EXEC uspSitePermission_Insert		           
						   @UserID = @UserID
						   ,@SiteID = @SiteID
						   ,@HistoryID = null
						   ,@IsDeleted = 0
						   ,@CreatedBy = @CreatedBy
						   ,@CreatedOn = null
						   ,@ContractorID = @ContractorID
						   ,@SitePermissionID = @SitePermissionID 
						   ,@Error_Number = @Error_Number OUTPUT
						   ,@Error_Message = @Error_Message OUTPUT

				SELECT @Pos = @NextPos+1
				SET @Num = @Num + 1
			END

		END

	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0	
			ROLLBACK TRANSACTION
		[b]SELECT @Error_Message[/b]
		[b]IF @Error_Message Is Null[/b]
		SELECT 
			 @Error_Number = ERROR_NUMBER()
			,@Error_Message = ERROR_MESSAGE()
		RAISERROR ('Error Adding', 16, 1)
	END CATCH

	IF @@TRANCOUNT > 0	
		COMMIT TRANSACTION

I'm not sure why, but the @Error_message (bolded) is null even though it's an output from the nested sp. What gives?

Thanks for the help!

 

[Qik3Coder]

You may need to save off the error message(s) prior to doing the rollback.


If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]