robthomas09
asked on
SQL Assigning Null Value to Duplicate Variables
Hello Experts,
I am using the following stored procedure to do an insert into a table.
I am mostly concerned with this section here.
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?
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);
ENDI 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)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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.