Raiserror doesn't output messages

Question

Raiserror doesn't output messages

Naomi Nosonovsky 8,881

Hi everybody,

I have a strange problem. I have a long procedure which has 2 places that send notes for other procedure. This procedure outputs NULL by this line of code:

DECLARE @message VARCHAR(200) = ' Notes is ' + ISNULL(@notes, 'NULL')
		 RAISERROR (@message,  0, 1) WITH NOWAIT

It outputs Notes is NULL.

In the calling procedure I have the following:

SET @notes = 'NPI ' + ISNULL(@npi,'') + ' excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD ('
									+ CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)) + 
									'LEN(@Snapshot) = ' + CAST(LEN(ISNULL(@Snapshot, 'NULL')) AS VARCHAR (10)) +
									') ' + ISNULL(ERROR_MESSAGE(),'');
                       RAISERROR (@notes, 0, 1) WITH NOWAIT
						EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
							@ExcludedId = @ProviderID
							, @ExtractType = @inp_extract_type
							, @notes = @notes;
					    
					FETCH NEXT FROM SplitProviderCursor
					INTO
						@myrowid
						, @Npi;
						SET @excludedProviders1 = @excludedProviders1+1
						RAISERROR (@notes, 0, 1) WITH NOWAIT
						RAISERROR (@excludedProviders1, 0, 1) WITH nowait
				END TRY
				BEGIN CATCH
					-- Add exclusion
					BEGIN TRY
						SET @notes = 'NPI ' + ISNULL(@npi,'') + ' excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD ('
									+  CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)) + ') ' + ISNULL(ERROR_MESSAGE(), 
									
								'');
						EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
							@ExcludedId = @ProviderID
							, @ExtractType = @inp_extract_type
							, @notes = @notes;
							SET @excludedProviders2 = @excludedProviders1+2
                          	RAISERROR (@excludedProviders2, 0, 1) WITH NOWAIT
                            RAISERROR (@notes, 0, 1) WITH NOWAIT

As you can see the Notes can't be null -I take every possible precaution against NULLs.

Also none of the RAISERROR messages are outputted and I don't understand why.

Can someone please explain this to me?

Thanks

Naomi Nosonovsky 8,881 Reputation points

2025-11-03T14:07:52.4+00:00

I added select @notes as notes and time this I got output NULL 2 times. There are two puzzling questions. First of all, I ran the procedure right before, so I except it to return nothing yet evetime it outputs 2 records. Secondly first time it produces a lot of records which is not excepted. And finally, why notes are NULL when every part of it in ISNULL statement. I'm going to add ISNULL for the whole @notes.

Naomi Nosonovsky 8,881

I changed the code to

SET @notes
                            = ISNULL('NPI ' + ISNULL(@Npi, '') + ' excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD ('
                              + CONVERT(VARCHAR(10), ISNULL(@inp_partition_id, 0)) + 'LEN(@Snapshot) = '
                              + CAST(LEN(ISNULL(@Snapshot, 'NULL')) AS VARCHAR(10)) + ') '
                              + ISNULL(ERROR_MESSAGE(), ''),'');
                    RAISERROR(@notes, 0, 1) WITH NOWAIT;
					SELECT @notes AS notes1

So, I expect to at least to see empty output both times (Actually, I don't expect to see any output after running procedure second time). Why am I still seeing NULL. This makes so sense.

Naomi Nosonovsky 8,881 Reputation points

2025-11-03T19:10:47.9733333+00:00

Also, I tried a few times to correct several typos in the question itself and it gave me an error every time.
Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-11-03T22:06:05.61+00:00
I don't see what you are doing inside that procedure, but you could use concat, to make your life easier:

SET @notes = conact('NPI ', @npi, ' excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD (' ISNULL(@inp_partition_id,0), 'LEN(@Snapshot) = ' + LEN(@Snapshot, ')', ERROR_MESSAGE())

With concat all arguments are implicitly converted to string and a NULL value becomes an empty string.
Naomi Nosonovsky 8,881 Reputation points

2025-11-03T23:19:35.1666667+00:00

Why RASERROR messages don't output? Also, almost all notes now show output, but the first one still shows NULL. Also, I am currently running this procedure and I'm unable to cancel the execution - there is no red cancel button.

Naomi Nosonovsky 8,881

SET @notes = CONCAT( 'NPI ' , isnull(@npi, '') , ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition (', 
						                     CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)),  
									 ' for region ' , CAST(@inp_region_nbr AS VARCHAR(20)),') ', ERROR_MESSAGE()) ;
						EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
							@ExcludedId = @ProviderID
							, @ExtractType = @inp_extract_type
							, @notes = @notes;
					 SET @excludedProviders2 = @excludedProviders1 + 2;
                        RAISERROR(@excludedProviders2, 0, 1) WITH NOWAIT;
                        RAISERROR(@notes, 0, 1) WITH NOWAIT;

Now outputs


![enter image description here](/api/attachments/82b2fa6d-d0ac-4be5-8b89-8bbd6c64b17d?platform=QnA)

I don't understand text about error number 2, etc.

Viorel 125.6K Reputation points

2025-11-04T07:58:10.5966667+00:00
I think that the unexpected messages from your image are caused by RAISERROR(@excludedProviders2, 0, 1), where @excludedProviders2 is an integer number like 22, 23, 24, etc., which has a special meaning (see documentation for RAISERROR). Remove this line if not needed. To display the number, try something like this:

declare @t varchar(max) = cast(@excludedProviders2 as varchar(max)) RAISERROR(@t, 0, 1) WITH NOWAIT

Check if there are other statements to adjust, like RAISERROR(@excludedProviders1, 0, 1), which cause the same situation.
Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-11-04T21:58:41.43+00:00
Another thing to watch out for is that your text may include a per cent character, which has a special meaning for RAISERROR. To avoid that risk, do this:

RAISERROR('%s', 0, 1, @notes)

In the same vein, to use a number:

RAISERROR('%d', 0, 1, @excludedProviders2)

Why you get back NULL, I don' t know. I don't see all of your code, so I can't tell what you are doing wrong.

Also, I am currently running this procedure and I'm unable to cancel the execution - there is no red cancel button.

Seems like you have managed to deleted from the toolbar. Restore it from the arrow at the end of the toolbar. You can also cancel from the Query menu.

Naomi Nosonovsky 8,881

The procedure has the following code which still produces NULL for notes somehow:

WHILE @@FETCH_STATUS = 0
            BEGIN
                BEGIN TRY
           --         TRUNCATE TABLE #tempTable;

                    EXECUTE dbo.usp_File_Extract_ProviderMasterFile_VA_SPLIT @inp_region_nbr = @inp_region_nbr,
                                                                             @inp_npi = @Npi,
                                                                             @XMLVARCHAR = @Snapshot OUTPUT,
                                                                             @providerid = @ProviderID OUTPUT;	 

                    --INSERT INTO #tempTable
                    --(
                    --    entity_id,
                    --    run_id,
                    --    partition_id, -- for splitting providers by Run/Partition ID
                    --    provider_id,
                    --    sha_value,
                    --    SNAPSHOT,
                    --    etl_create_dttm
                    --)
      --              VALUES
						--(@Npi
						--, @inp_run_id
						--, @inp_partition_id
						--, @ProviderID
						--, HASHBYTES('SHA2_512',   @Snapshot)
						--, @Snapshot
						--, CAST(SYSDATETIMEOFFSET()AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));                  	

/*
					INSERT INTO
						dbo.PROVIDER_EXTRACT_TEMP_MOD
						(
							entity_id
							, run_id
							, partition_id -- for splitting providers by Run/Partition ID
							, provider_id
							, sha_value
							, snapshot
							, etl_create_dttm
						)
					VALUES
						(@Npi
						, @inp_run_id
						, @inp_partition_id
						, @ProviderID
						, HASHBYTES('SHA2_512',   @Snapshot)
						, @Snapshot
						, CAST(SYSDATETIMEOFFSET()AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));
*/

                    IF LEN(@Snapshot) < 1067142
                       AND @Snapshot IS NOT NULL
                        INSERT INTO dbo.PROVIDER_EXTRACT_TEMP_MOD
                        (
                            entity_id,
                            run_id,
                            partition_id, -- for splitting providers by Run/Partition ID
                            provider_id,
                            sha_value,
                            snapshot,
                            etl_create_dttm
                        )
                        --SELECT entity_id,
                        --       run_id,
                        --       partition_id, -- for splitting providers by Run/Partition ID
                        --       provider_id,
                        --       sha_value,
                        --       SNAPSHOT,
                        --       etl_create_dttm
                        --FROM #tempTable;
							
					VALUES
						(@Npi
						, @inp_run_id
						, @inp_partition_id
						, @ProviderID
						, HASHBYTES('SHA2_512',   @Snapshot)
						, @Snapshot
						, CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));

                    ELSE                        

                     SET @notes = CONCAT('NPI ', @npi, ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for patrition ',
                     ISNULL(@inp_partition_id,0), ' LEN(@Snapshot) = ' , LEN(ISNULL(@Snapshot,'')) , 
					 ' for region ' , CAST(@inp_region_nbr AS VARCHAR(20)), ERROR_MESSAGE())

                    
                    EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod @ExcludedId = @ProviderID,
                                                             @ExtractType = @inp_extract_type,
                                                             @notes = @notes;


                    FETCH NEXT FROM SplitProviderCursor
                    INTO @myrowid,
                         @Npi;
                    
                   
                END TRY
                BEGIN CATCH
                    -- Add exclusion
                    BEGIN TRY

                        SET @notes = CONCAT( 'NPI ' , isnull(@npi, '') , ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition ', 
						                     CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)),  
									 ' for region ' , CAST(@inp_region_nbr AS VARCHAR(20)),'  ', ERROR_MESSAGE()) ;

						SET @notes = ISNULL(@notes,'No notes')  
						EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
							@ExcludedId = @ProviderID
							, @ExtractType = @inp_extract_type
							, @notes = @notes;	

					END TRY
					BEGIN CATCH
						-- If this doesn't work, just audit error and keep going.
						SET @audit_message_txt = CONCAT( 'Failed to exclude NPI ' , CONVERT(VARCHAR(20), ISNULL(@Npi, 0)) , 
						' for region '  , CAST(@inp_run_id AS VARCHAR(10)) , ' ', ERROR_MESSAGE());

						EXECUTE dbo.usp_write_audit
							@inp_process_txt = @inp_process_txt
							, @inp_sub_process_txt = @sub_process_txt
							, @inp_type_id = 0
							, @inp_action_txt = @audit_message_txt
							, @inp_load_file_detail_id = @efd
							, @inp_job_nbr = @inp_load_report_job_nbr;
					END CATCH;                   

                END CATCH;

            END;

Which produces :

Notes is NULL

Notes is NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.