Solved

SQL Assigning Null Value to Duplicate Variables

Posted on 2014-02-12
1
441 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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…

630 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