Update Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Stored Procedure

Author

Topic

taj
Starting Member

39 Posts

Posted - 2013-09-21 : 05:33:08

Hi all,

I have a Table by name LAB_TEST_MASTER(MASTER TABLE)with Test_ID,Test_Name and Normal_Values columns.

I have one more table by name PATIENT_LAB_TESTS (TRANSACTION TABLE) with Test_ID,Test_Result,and Test_Status Columns.

In Master Table for a Test_ID 100(FASTING BLOOD SUGAR) if my Normal_Values range from 10-20(Low Value to High Value)

In my Transaction Table if my Test_Result Column is having 30 Result for Test_ID 100.

How to compare the Test_Result Column With Normal Values and Update the Test_Status Column in Transaction Table.

Below is the actual SP which am trying to work as per requrment.

Requirement:
1. If the value in the Results_V column is below the normal range(Low Value) then i should update the status_c column as 'L'
2. If the value in the Results_V column is above the normal range(High Value) then i should update the status_c column as 'H'
3. If the value in the Results_V column is between the normal range
then i should update the status_c column as ' ' (Empty Space)

Below is my SP

CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT]
@Test_Code VARCHAR(10),
@Patient_ID VARCHAR(10),
@Normal_value varchar(20) ,
@Result_v VARCHAR(10)
AS
BEGIN
UPDATE PLT SET PLT.STATUS_C =
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L'
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H'
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N'
END
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = @Test_Code
WHERE PATIENT_ID_N=@Patient_ID
END

Issues

1. Am not able to compare decimal values
2. The stored procedure retaining its last value of status column

allan8964
Posting Yak Master

249 Posts

Posted - 2013-09-21 : 14:07:54

I think your design for table LAB_TEST_MASTER is against the Normalization rules. According to 1NF rule no rows should contain values like 10-20, or firstName + lastName. That's why your codes in sp to pick up low and high values are ambigious, like this: < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1). If you modify the master table to hold values range in 2 new columns like ValueMin float, ValueMax float, then you save 10 in ValueMin and 20 in ValueMax. Then in sp codes you can pick up data by CASE WHEN PLT.RESULTS_V < LMT.ValueMin ... something like this ... then you won't have decimal issue. By the way you should use float instead of decimal in your PLT table ...

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-21 : 14:28:16

quote:
Originally posted by taj

Hi all,

I have a Table by name LAB_TEST_MASTER(MASTER TABLE)with Test_ID,Test_Name and Normal_Values columns.

I have one more table by name PATIENT_LAB_TESTS (TRANSACTION TABLE) with Test_ID,Test_Result,and Test_Status Columns.

In Master Table for a Test_ID 100(FASTING BLOOD SUGAR) if my Normal_Values range from 10-20(Low Value to High Value)

In my Transaction Table if my Test_Result Column is having 30 Result for Test_ID 100.

How to compare the Test_Result Column With Normal Values and Update the Test_Status Column in Transaction Table.

Below is the actual SP which am trying to work as per requrment.

Requirement:
1. If the value in the Results_V column is below the normal range(Low Value) then i should update the status_c column as 'L'
2. If the value in the Results_V column is above the normal range(High Value) then i should update the status_c column as 'H'
3. If the value in the Results_V column is between the normal range
then i should update the status_c column as ' ' (Empty Space)

Below is my SP

CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT]
@Test_Code VARCHAR(10),
@Patient_ID VARCHAR(10),
@Normal_value varchar(20) ,
@Result_v VARCHAR(10)
AS
BEGIN
UPDATE PLT SET PLT.STATUS_C =
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L'
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H'
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N'
END
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = @Test_Code
WHERE PATIENT_ID_N=@Patient_ID
END

Issues

1. Am not able to compare decimal values
2. The stored procedure retaining its last value of status column

Your description and the code does not seem to match. In your description, I see Test_ID is a column that is common to both tables. Yet, in your stored procedure, you are not even using that column. You should be joining on that column. Are there other columns in the table that you are using to join?

Perhaps this?

CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT] 
@Test_Code VARCHAR(10), 
@Patient_ID VARCHAR(10), 
@Normal_value varchar(20) , 
@Result_v VARCHAR(10) 
AS 
BEGIN 
UPDATE PLT SET PLT.STATUS_C = 
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L' 
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H' 
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N' 
END 
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = PLT.TEST_CODE
WHERE PATIENT_ID_N=@Patient_ID AND LMT.TEST_CODE_N = @Test_Code
END

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-21 : 14:30:25

And, also forgot to mention the following:

While the physicians want to see normal ranges as 20-40 etc., it is not a good idea to keep the ranges as a string like that. You should have separate columns for low, high, very low, very high, panic low, panic high, or whatever else ranges you may want to apply.

Also, there are some tests for which the low/high range does not really apply. <100 might be normal for some tests, for example.

taj
Starting Member

39 Posts

Posted - 2013-09-23 : 04:08:18

Hi James,

Thanks for your suggestion,
Can u please send the code for updating status column value after separating the columns for high and low.

taj
Starting Member

39 Posts

Posted - 2013-09-23 : 06:24:37

quote:
Originally posted by James K

quote:
Originally posted by taj

Hi all,

I have a Table by name LAB_TEST_MASTER(MASTER TABLE)with Test_ID,Test_Name and Normal_Values columns.

I have one more table by name PATIENT_LAB_TESTS (TRANSACTION TABLE) with Test_ID,Test_Result,and Test_Status Columns.

In Master Table for a Test_ID 100(FASTING BLOOD SUGAR) if my Normal_Values range from 10-20(Low Value to High Value)

In my Transaction Table if my Test_Result Column is having 30 Result for Test_ID 100.

How to compare the Test_Result Column With Normal Values and Update the Test_Status Column in Transaction Table.

Below is the actual SP which am trying to work as per requrment.

Requirement:
1. If the value in the Results_V column is below the normal range(Low Value) then i should update the status_c column as 'L'
2. If the value in the Results_V column is above the normal range(High Value) then i should update the status_c column as 'H'
3. If the value in the Results_V column is between the normal range
then i should update the status_c column as ' ' (Empty Space)

Below is my SP

CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT]
@Test_Code VARCHAR(10),
@Patient_ID VARCHAR(10),
@Normal_value varchar(20) ,
@Result_v VARCHAR(10)
AS
BEGIN
UPDATE PLT SET PLT.STATUS_C =
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L'
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H'
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N'
END
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = @Test_Code
WHERE PATIENT_ID_N=@Patient_ID
END

Issues

1. Am not able to compare decimal values
2. The stored procedure retaining its last value of status column

Your description and the code does not seem to match. In your description, I see Test_ID is a column that is common to both tables. Yet, in your stored procedure, you are not even using that column. You should be joining on that column. Are there other columns in the table that you are using to join?

Perhaps this?
CREATE PROC[dbo].[PATIENT_UPDATE_LAB_RESULT]
@Test_Code VARCHAR(10),
@Patient_ID VARCHAR(10),
@Normal_value varchar(20) ,
@Result_v VARCHAR(10)
AS
BEGIN
UPDATE PLT SET PLT.STATUS_C =
CASE WHEN PLT.RESULTS_V < LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'L'
WHEN PLT.RESULTS_V > RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'H'
WHEN PLT.RESULTS_V BETWEEN LEFT(@Normal_value, CHARINDEX('-', @Normal_value)-1) AND RIGHT(@Normal_value, CHARINDEX('-', @Normal_value)-1) THEN 'N'
END
FROM PATIENT_LAB_TESTS PLT
INNER JOIN LAB_TEST_MASTER LMT ON LMT.TEST_CODE_N = PLT.TEST_CODE
WHERE PATIENT_ID_N=@Patient_ID AND LMT.TEST_CODE_N = @Test_Code
END

Thanks for ur suggestion, If the use the above given code th status column is repeated by 'H' as values the values is not updating other than the 'H' value

Please suggest me some better answer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources