Raiserror doesn't output messages
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
Naomi Nosonovsky
8,861
Reputation points
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
SQL Server | SQL Server Transact-SQL
{count} votes
-
Naomi Nosonovsky 8,861 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,861 Reputation points
2025-11-03T14:18:46.7766667+00:00 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 notes1So, 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,861 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 128K Reputation points MVP Volunteer Moderator2025-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,861 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,861 Reputation points
2025-11-04T00:11:49.08+00:00 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
I don't understand text about error number 2, etc.
-
Viorel 125.2K Reputation points2025-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 NOWAITCheck if there are other statements to adjust, like RAISERROR(@excludedProviders1, 0, 1), which cause the same situation.
-
Erland Sommarskog 128K 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,861 Reputation points
2025-11-05T16:43:38.4766667+00:00 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 NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
which I cannot figure out.
My colleague's code which is practically the same doesn't output messages at all:
WHILE @@FETCH_STATUS = 0
BEGIN BEGIN TRY 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 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))); FETCH NEXT FROM SplitProviderCursor INTO @myrowid , @Npi; END TRY BEGIN CATCH -- Add exclusion BEGIN TRY SET @notes = 'NPI excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD (' + CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)) + ')'; EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod @ExcludedId = @ProviderID , @ExtractType = @inp_extract_type , @notes = @notes; --RAISERROR (@notes, 0, 1) END TRY BEGIN CATCHI don't see much difference.
-
Naomi Nosonovsky 8,861 Reputation points
2025-11-05T17:08:18.5+00:00 I'm running it now again and getting this output:
Notes is NULL Notes is NPI 1003009051 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. Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value. Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value. Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value. Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value. Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value.What is strange is why I'm getting NULL still and the second puzzle is why the same exact NPI repeated multiple times and why the first line shows different error than all other errors.
-
Viorel 125.2K Reputation points
2025-11-05T17:12:45.1633333+00:00 Maybe the Debugger will help too:
-
Naomi Nosonovsky 8,861 Reputation points
2025-11-05T17:52:56.7+00:00 So, if I remove this condition
--IF LEN(@Snapshot) < 1067142 -- AND @Snapshot IS NOT NULLthen there is no error.
-
Erland Sommarskog 128K Reputation points MVP Volunteer Moderator
2025-11-05T22:01:46.15+00:00 Yeah, the code you posted above will either insert a row or set the @notes variable.
Which is not that easy to see, because the code is inconsistently indented and cluttered with code that is commented out. It would be a courtesy to the people that you are asking to help to clean up the code before you post. And who knows, you may spot the error yourself.
-
Naomi Nosonovsky 8,861 Reputation points
2025-11-06T19:58:37.2466667+00:00 This is my latest code:
DROP TABLE IF EXISTS #tempTable; CREATE TABLE #tempTable ( entity_id BIGINT NOT NULL, run_id INT, partition_id INT, -- for splitting providers by Run/Partition ID provider_id BIGINT, sha_value VARBINARY(64), SNAPSHOT NVARCHAR(MAX), etl_create_dttm DATETIME ); DECLARE @myrowid INT; DECLARE SplitProviderCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT rowid, npi FROM #tmp_npi_list; OPEN SplitProviderCursor; FETCH NEXT FROM SplitProviderCursor INTO @myrowid, @Npi; 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, 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))); DECLARE @snapshotLen bigint SELECT @snapshotLen = LEN(CAST(snapshot as nvarchar(max))) FROM #tempTable RAISERROR ('%d', 0, 1, @snapshotLen) IF @SnapshotLen < 1067142 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 ', CAST(@npi AS VARCHAR(100)), ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for patrition ', CAST(ISNULL(@inp_partition_id,0) AS VARCHAR(10)), ' LEN(@Snapshot) = ' , CAST(ISNULL(@snapshotLen,0) AS varchar(10)) , ' for region ' , CAST(ISNULL(@inp_region_nbr,0) AS VARCHAR(20)), ISNULL(ERROR_MESSAGE(),'')) SET @notes = ISNULL(@notes, 'Empty') EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod @ExcludedId = @ProviderID, @ExtractType = @inp_extract_type, @notes = @notes; 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(ISNULL(@inp_region_nbr,0) 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; FETCH NEXT FROM SplitProviderCursor INTO @myrowid, @Npi; END;And the output is:
Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is Empty Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 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. Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varcharwhich I don't understand. Why is shows Notes is Empty and why it outputs this strange error multiple times for the same NPI?
-
Viorel 125.2K Reputation points
2025-11-06T20:12:06.1633333+00:00 As indicated, you should use BEGIN...END with each complex IF and ELSE to clearly delimit the block of instructions.
-
Naomi Nosonovsky 8,861 Reputation points
2025-11-06T21:32:34.07+00:00 Exactly, I changed the code to the following it it worked (no more messages), but the Raiserror didn't output ShortLen:
DROP TABLE IF EXISTS #tempTable; CREATE TABLE #tempTable ( entity_id BIGINT NOT NULL, run_id INT, partition_id INT, -- for splitting providers by Run/Partition ID provider_id BIGINT, sha_value VARBINARY(64), SNAPSHOT NVARCHAR(MAX), etl_create_dttm DATETIME ); DECLARE @myrowid INT; DECLARE SplitProviderCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT rowid, npi FROM #tmp_npi_list; OPEN SplitProviderCursor; FETCH NEXT FROM SplitProviderCursor INTO @myrowid, @Npi; 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, provider_id, sha_value, SNAPSHOT, etl_create_dttm ) VALUES (@Npi, @inp_run_id, @inp_partition_id, @ProviderID, HASHBYTES('SHA2_512', @Snapshot), @Snapshot, CURRENT_TIMESTAMP); -- CAST(SYSDATETIMEOFFSET()AT TIME ZONE 'Central Standard Time' AS DATETIME2(7 DECLARE @snapshotLen BIGINT; SELECT @snapshotLen = ISNULL(LEN(CAST(SNAPSHOT AS NVARCHAR(MAX))), 0) FROM #tempTable; RAISERROR('%d', 0, 1, @snapshotLen); IF ISNULL(@snapshotLen, 0) < 1067142 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 BEGIN SET @notes = CONCAT( 'NPI ', CAST(ISNULL(@Npi, '') AS VARCHAR(100)), ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for patrition ', CAST(ISNULL(@inp_partition_id, 0) AS VARCHAR(10)), ' LEN(@Snapshot) = ', CAST(ISNULL(@snapshotLen, 0) AS VARCHAR(10)), ' for region ', CAST(ISNULL(@inp_region_nbr, 0) AS VARCHAR(20)), ISNULL(ERROR_MESSAGE(), '') ); SET @notes = ISNULL(@notes, 'Empty'); EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod @ExcludedId = @ProviderID, @ExtractType = @inp_extract_type, @notes = @notes; END; 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(ISNULL(@inp_region_nbr, 0) AS VARCHAR(20)), ' ', ISNULL(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; FETCH NEXT FROM SplitProviderCursor INTO @myrowid, @Npi; END; -
Erland Sommarskog 128K Reputation points MVP Volunteer Moderator
2025-11-06T22:19:48.9966667+00:00 Change
' LEN(@Snapshot) = ', CAST(ISNULL(@snapshotLen, 0) AS VARCHAR(10)),To
' LEN(@Snapshot) = ', @snapshotLen,Same goes for all the other unnecessary CAST and ISNULL. Concat handles that for you.
Sign in to comment
Sign in to answer