| Author |
Topic |
|
stevenandler
Starting Member
42 Posts |
 Posted - 2013-01-30 : 14:32:04
|
I am trying to figure out how to return fields from a select statement performed in one Stored Procedure to another
This is a snippet from the calling Stored Prcedure:
EXECUTE OGEN.VALIDATE_PATIENT @VALIDATED OUTPUT, @LMRN, @LASTNAME, @FIRSTNAME, @SSN, @DOB
PRINT @VALIDATED
This is the complete code from the called Stored Precedure:
ALTER PROCEDURE [OGEN].[VALIDATE_PATIENT]
(@VALIDATED INT OUTPUT, @MRN CHAR(10), @LAST_NAME VARCHAR(30), @FIRST_NAME VARCHAR(30),@SSN CHAR(9), @DOB datetime)
AS
BEGIN
SELECT PAT_NUMBER FROM OGEN.GEN_M_PATIENT_MAST WHERE ((@MRN=MRN AND UPPER(@LAST_NAME) = LAST_NAME) OR (@MRN=MRN AND @DOB = BIRTH_DATE) OR
(@MRN=MRN AND UPPER(@FIRST_NAME) = FIRST_NAME))
SET @VALIDATED = @@ROWCOUNT
END
I would like to return the values of PATIENT_ID and FACILITY_KEY |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
stevenandler
Starting Member
42 Posts |
Posted - 2013-01-30 : 15:30:49
|
I added the following code to the stored procedure
ALTER PROCEDURE [OGEN].[VALIDATE_PATIENT]
(@VALIDATED INT OUTPUT, @MRN CHAR(10), @LAST_NAME VARCHAR(30), @FIRST_NAME VARCHAR(30),@SSN CHAR(9), @DOB datetime)
AS
BEGIN
DECLARE @PATIENT_ID INT
DECLARE @FACILITY_KEY CHAR(4)
-- 1st attempt
SELECT * FROM OGEN.GEN_M_PATIENT_MAST WHERE ((@MRN=MRN AND UPPER(@LAST_NAME) = LAST_NAME) OR (@MRN=MRN AND @DOB = BIRTH_DATE) OR
(@MRN=MRN AND UPPER(@FIRST_NAME) = FIRST_NAME))
SET @VALIDATED = @@ROWCOUNT
SET @PATIENT_ID = PATIENT_ID
SET @FACILITY_KEY = FACILITY_KEY
END
when I attempt to alter the procedure, it fails with the following messages:
Msg 207, Level 16, State 1, Procedure VALIDATE_PATIENT, Line 17
Invalid column name 'PATIENT_ID'.
Msg 207, Level 16, State 1, Procedure VALIDATE_PATIENT, Line 18
Invalid column name 'FACILITY_KEY'.
I am positive that these two fields are defined in the table.
|
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-01-30 : 17:19:23
|
You need to be aware that if @@rowcount > 1 then more than one row satisfied the condition in your WHERE clause. BUT @facility_key and @patient_id will be for just one of those rows.
Be One with the Optimizer
TG |
|
|
|
stevenandler
Starting Member
42 Posts |
Posted - 2013-01-30 : 17:52:05
|
| Thank you Tara and TG. It works fine. I will probably add the max aggregate to ensure I get only one row of data |
|
|
|
|
Return fields from a select to a proc