Solved

SQL Assigning Null Value to Duplicate Variables

Posted on 2014-02-12
1
427 Views
Last Modified: 2014-02-12
Hello Experts,

I am using the following stored procedure to do an insert into a table.

USE [NGTest]
GO
/****** Object:  StoredProcedure [dbo].[LMC_Finalize_Split_Procedure3]    Script Date: 02/12/2014 14:49:55 ******/
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)

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

if @diag_code_1 is null
    begin
	     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
	     set @diag_code_2 = 'V70.0'
	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
	     set @diag_code_3 = 'V70.0'
	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
	     set @diag_code_4 = 'V70.0'
	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


I am mostly concerned with this section here.

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)

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

if @diag_code_1 is null
    begin
	     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
	     set @diag_code_2 = 'V70.0'
	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
	     set @diag_code_3 = 'V70.0'
	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
	     set @diag_code_4 = 'V70.0'
	end
	
set @lib_id_4 = (select diagnosis_code_lib_id from diagnosis_code_mstr where icd9cm_code_id = @diag_code_4)

Open in new window


Essentially in the insert later on in the procedure i can only enter the 'V70.0' into one of the diagnosis_code_id columns ([diagnosis_code_id_1] through [diagnosis_code_id_4]) or else it will cause errors. If 2 or more of the diag_code variables have a value 'V70.0' i only need one of them to hold the variable and the rest need to be set back to null. Any ideas on a how to accomplish this?
0
Comment
Question by:robthomas09
1 Comment
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39854457
you can try the below code

DECLARE @TEMP INT
SET @TEMP = 0

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

Open in new window

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

863 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

25 Experts available now in Live!

Get 1:1 Help Now