Solved

SQL Assigning Null Value to Duplicate Variables

Posted on 2014-02-12
1
424 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Complex SQL 10 33
SQL Help - 12 37
Fixed Length SQL Query Question 3 9
Convert char to decimal in a SQL Server View 14 12
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

707 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

18 Experts available now in Live!

Get 1:1 Help Now