Solved

SQL rows being inserted with incorrect null values.

Posted on 2014-02-26
3
408 Views
Last Modified: 2014-03-13
Hello Experts,

I am working with the following stored procedure.

USE [NGTest]
GO
/****** Object:  StoredProcedure [dbo].[LMC_Finalize_Split_Procedure3]    Script Date: 02/25/2014 17:58:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[LMC_Finalize_Split_Procedure3]
  @enterprise_id VARCHAR(5),
  @practice_id VARCHAR(4),
  @person_id CHAR(36),
  @enc_timestamp DATETIME,
  @user_id       INT,
  @enc_id        VARCHAR(36),
  @cpt_code		 VARCHAR(30),
  @provider_id   VARCHAR(36),
  @location_id   VARCHAR(36), 
  @offset		 int	-- This procedur number, to offset the ICDs
  
AS

BEGIN	
	DECLARE @pp_uniq_id UNIQUEIDENTIFIER; 
	DECLARE @serviceItemLibraryID UNIQUEIDENTIFIER;
	DECLARE @serviceItem varchar(100);
	
	SET @serviceItemLibraryID = '00CB861F-B72F-47B9-8BAD-669ED330B3E2';-- [LMA] hardcoded! probably need to be changed in future versions
	SET @pp_uniq_id = NEWID(); 
	
	-- (LMA) The service Item should not be confused with the CPT Code. They could be different. 
	SELECT @serviceItem = service_item_id
	FROM service_item_mstr 
	WHERE cpt4_code_id = @cpt_code
	AND service_item_lib_id = @serviceItemLibraryID;
	
	IF @serviceItem IS NULL 
	BEGIN
		/* debug infpormation 
		--INSERT INTO lma_debug (text) values ('Could not find a service item for ' + @cpt_code);
		
		PRINT "Invalid Service Item ID"; */
		RETURN
	END
	
	
		--,(select isnull(dx_code, 'V70.0') from ##ICDs where row_num =  (@offset * 4) -3)
--           ,(select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = 
--								(select isnull(dx_code, 'V70.0') from ##ICDs where row_num =  (@offset * 4) -3 )
								
declare @diag_code_1 varchar(10)
declare @lib_id_1 varchar(36)
declare @diag_code_2 varchar(10)
declare @lib_id_2 varchar(36)
declare @diag_code_3 varchar(10)
declare @lib_id_3 varchar(36)
declare @diag_code_4 varchar(10)
declare @lib_id_4 varchar(36)

DECLARE @TEMP INT
SET @TEMP = 0

set @diag_code_1 = (select dx_code from ##ICDs where row_num =  (@offset * 4) -3)

if @diag_code_1 is null
    begin
             SET @TEMP = 1
	     set @diag_code_1 = 'V70.0'
	end
	
set @lib_id_1 = (select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = @diag_code_1)



set @diag_code_2 = (select dx_code from ##ICDs where row_num =  (@offset * 4) -2)

if @diag_code_2 is null
    begin
             IF @TEMP = 0
             BEGIN 
	         set @diag_code_2 = 'V70.0'
                 SET @TEMP = 1
             END
	end
	
set @lib_id_2 = (select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = @diag_code_2)



set @diag_code_3 = (select dx_code from ##ICDs where row_num =  (@offset * 4) -1)

if @diag_code_3 is null
    begin
             IF @TEMP = 0
             BEGIN
	        set @diag_code_3 = 'V70.0'
                SET @TEMP = 1
             END
	end
	
set @lib_id_3 = (select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = @diag_code_3)



set @diag_code_4 = (select dx_code from ##ICDs where row_num =  (@offset * 4))

if @diag_code_4 is null
    begin
             IF @TEMP = 0
             BEGIN
	         set @diag_code_4 = 'V70.0'
                 SET @TEMP = 1
             END
	end

set @lib_id_4 = (select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = @diag_code_4)


	INSERT INTO [dbo].[patient_procedure]
           ([uniq_id]
           ,[enterprise_id]
           ,[practice_id]
           ,[person_id]
           ,[enc_id]
           ,[service_item_lib_id]
           ,[service_item_group_name]
           ,[service_item_id]
           ,[service_item_desc]
           ,[cpt4_code_id]
           ,[service_date]
           ,[provider_id]
           ,[referring_provider_name]
           ,[date_resolved]
           ,[diagnosis_code_id_1]
           ,[diagnosis_code_lib_id_1]
           ,[diagnosis_code_id_2]
           ,[diagnosis_code_lib_id_2]
           ,[diagnosis_code_id_3]
           ,[diagnosis_code_lib_id_3]
           ,[diagnosis_code_id_4]
           ,[diagnosis_code_lib_id_4]
           ,[place_of_service]
           ,[accept_assign_ind]
           ,[units]
           ,[location_id]
           ,[amount]
           ,[suppress_billing_ind]
           ,[tooth]
           ,[surface]
           ,[quadrant]
           ,[delete_ind]
           ,[note]
           ,[created_by]
           ,[modified_by]
           ,[create_timestamp]
           ,[modify_timestamp]
           ,[start_time]
           ,[stop_time]
           ,[total_time]
		   ,[alt_code]
           ,[anesthesia_billing_ind]
           ,[diagnosis_code_id_5]
           ,[diagnosis_code_lib_id_5]
           ,[diagnosis_code_id_6]
           ,[diagnosis_code_lib_id_6]
           ,[diagnosis_code_id_7]
           ,[diagnosis_code_lib_id_7]
           ,[diagnosis_code_id_8]
           ,[diagnosis_code_lib_id_8]
           ,[source_product_id]
           ,[create_timestamp_tz]
           ,[modify_timestamp_tz]
           ,[rx_on_file_ind]
           ,[national_drug_code])
     VALUES
           (@pp_uniq_id
           ,@enterprise_id
           ,@practice_id
           ,@person_id
           ,@enc_id
           ,@serviceItemLibraryID
           ,''
           ,@serviceItem
           ,(select top 1 description from cpt4_code_mstr where cpt4_code_id = @cpt_code)
           ,@cpt_code
           ,(select convert(nvarchar(8), enc_timestamp, 112) from patient_encounter where enc_id = @enc_id)
           ,@provider_id
           ,''
           ,''
           ,@diag_code_1
           ,@lib_id_1
           ,@diag_code_2
           ,@lib_id_2
           ,@diag_code_3
           ,@lib_id_3
           ,@diag_code_4
           ,@lib_id_4
           ,''  --111  --!!!!!!!????????? [place_of_service]
           ,''
           ,1
           ,@location_id
           ,(select top 1 current_price from service_item_mstr where cpt4_code_id =  @cpt_code) --!!!!!!!????????? [amount]
           ,'N'
           ,''
           ,''
           ,''
           ,'N'
           ,''
           ,@user_id
           ,@user_id
           ,CURRENT_TIMESTAMP
           ,CURRENT_TIMESTAMP
           ,''
           ,''
           ,''
           ,''
           ,'N'
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,'EHR'
           ,0
           ,0
           ,'N'
           ,'')
           
		-- [LMA] Link to pending charges so EPM knows there are pending charges
		INSERT INTO pending_charge
			(practice_id, uniq_id, enc_id, create_timestamp, created_by, modify_timestamp, modified_by)
		VALUES
			(@practice_id, @pp_uniq_id, @enc_id, CURRENT_TIMESTAMP, @user_id, CURRENT_TIMESTAMP, @user_id); 

END

Open in new window


Here is what a sample set of data from the ##ICDs table looks like

row_num	dx_code
1	427.32
2	250.40
3	255.9
4	428.0
5	402.9
6	280.9
7	493.20
8	V49.72
9	535.50
10	583.81
11	153.9
12	287.50
13	300.00
14	252.00
15	413.9
16	278.00
17	274.9
18	440.1

Open in new window


When running the stored procedure there is an error when it runs the INSERT INTO [dbo].[patient_procedure] portion.

When the @offset variable has a value of 3 the codes that correspond to rows 9,10,11, and 12 in the ##ICDs table get inserted as 'null' values and i'm not entirely sure why this is happening.

Could it possibly be a problem with the logic here that is pulling the dx_code into the variable, perhaps a math or rounding error on the (@offset * 4) - 3 portion?

set @diag_code_1 = (select dx_code from ##ICDs where row_num =  (@offset * 4) -3)

Open in new window

0
Comment
Question by:robthomas09
3 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Most likely there is no matching data for the statement that generates the value to be inserted on that row/column wherever you populate that global temp table ##ICDs
0
 

Author Comment

by:robthomas09
Comment Utility
Hi lcohan,

If i'm understanding correctly your saying that the problem lies with the ##ICDs table not getting populated with data? The ##ICDs temp table is getting populated with the result set that i posted above.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
IF @offset is NULL, (@offset * 4) -3 is NULL; therefore, I also would check that your @offset parameter/variable is set as you expected.

Maybe it should default?  (COALESCE(@offset, 1) * 4) -3
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now